【Teradata SQL】日期类型计算

时间：2024-02-16 13:52:26

1.EXTRACT(抽取年/月/日/时/分/秒) 

//抽取年/月/日/时/分/秒
SELECT EXTRACT (YEAR FROM CURRENT_DATE); 
SELECT EXTRACT (MONTH FROM CURRENT_DATE+90); 
SELECT EXTRACT (DAY FROM \'1996-12-12\'); 

选项：YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | TIMEZONE_HOUR | TIMEZONE_MINUTE

2. 差值计算

//日期差值（年/月/日）==只写day、month、year差值最大99天、99月、99年
select (DATE\'1995-02-02\'  -  DATE\'1995-01-01\') day(4);   //天数，差值最大9999天。 
select (date \'1908-05-01\' - date \'1900-01-01\') month(4); //月数，差值最大9999月。
select (DATE\'1999-02-02\'  -  DATE\'1995-01-01\') year(4);  //年数，差值最大9999年。
//时间差值（小时/分/秒）
select (end_time - start_time) HOUR(4)  //小时，差值最大9999h
select (end_time - start_time) MINUTE(4) //分钟，差值最大9999m 
select (end_time - start_time) Second(4) //秒，差值最大9999s
SELECT MONTHS_BETWEEN(DATE\'1995-02-02\', DATE\'1995-01-01\'); //月数

3.日期时间加减

sel current_timestamp - interval \'1\' hour
sel current_date + interval \'1\' day

选项：year | month | day | hour | minute | second
cast(((a.aactl_tch_end_tmstp - a.actl_tch_start_tmstp) second(4)) as integer)>=15 
==>报错超出限制，改为如下语句
a.actl_tch_end_tstp > (a.actl_tch_start_tmstp + interval \'15\' second )
SELECT ADD_MONTHS (\'1999-08-15\' , 1);  //返回1999-09-15
SELECT ADD_MONTHS (\'1999-09-30\' , -1); //返回1999-08-30

4.LAST_DAY(月底日期)

SELECT LAST_DAY(DATE \'2009-12-20\');                //返回2019-12-31
SELECT LAST_DAY(TIMESTAMP \'2009-08-25 10:14:59\');  //返回2009-08-31

5.NEXT_DAY(下一个周几日期)

SELECT NEXT_DAY(DATE \'2009-12-20\', \'TUESDAY\'); //返回2009-12-22
SELECT NEXT_DAY(DATE \'2009-12-20\', \'FRIDAY\');  //返回2009-12-20

选项：SUNDAY  |  MONDAY  |  TUESDAY  |  WEDNESDAY  |   THURSDAY  |  FRIDAY  |  SATURDAY 前三位简写也可以如SUN

6.NUMTOYMINTERVAL(年月个数)

SELECT NUMTOYMINTERVAL(100, \'MONTH\'); //返回08-04，即8年4个月
SELECT NUMTOYMINTERVAL(40, \'YEAR\');   //返回40-00，即40年0个月

 

相关文章