2024年4月12日发(作者：)
高品质SAP技术群：455101474 matinal
跟我学HANA系列之日期函数总结（SAP HANA Platform Core 2.0 SPS 00）
更多资料请加SAP群：455101474
1、 ADD_DAYS Function (Datetime)
计算指定日期加指定天数后的日期
语法：ADD_DAYS (
举例：计算当前日期加30天后的日期
2、 ADD_MONTHS Function (Datetime)
计算指定日期加指定月后的日期
语法：ADD_MONTHS (
参数：DATE、TIMESTAMP、SECONDDATE
举例：后面的例子就直接写SQL了，不再贴图
SELECT ADD_MONTHS (TO_DATE (CURRENT_DATE, 'YYYY-MM-DD'), 1) "add months" FROM
DUMMY;
3、 ADD_MONTHS_LAST Function (Datetime)
计算指定的日期加上指定的月份数，如果输入日期是一个月的最后一天，则输出日期是
该月份的最后一天，即使这两个日期不同。
语法：ADD_MONTHS_LAST (
参数：DATE、TIMESTAMP、SECONDDATE
举例：SELECT ADD_MONTHS_LAST (TO_DATE (CURRENT_DATE, 'YYYY-MM-DD'), 1) "add
months last" FROM DUMMY;
4、 ADD_SECONDS Function (Datetime)
计算指定时间加指定的秒数。
语法：ADD_SECONDS (
举例：SELECT ADD_SECONDS (TO_TIMESTAMP ('2016-12-26 09:30:45'), 60*30) "add
seconds" FROM DUMMY;
5、 ADD_WORKDAYS Function (Datetime)
通过添加多个工作日起始日期计算日期。
语法：ADD_WORKDAYS (
TFACS bitfield
0
1
1
0
0
1
2
3
4
5
Day of the month Reason for not working
Public Holiday
Weekend
Weekend
高品质SAP技术群：455101474 matinal
TFACS bitfield
0
1
1
1
1
0
0
1
1
1
1
1
0
0
1
1
1
1
1
0
0
1
1
1
1
1
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Day of the month Reason for not working
Public Holiday
Weekend
Weekend
Weekend
Weekend
Weekend
Weekend
举例：SELECT ADD_WORKDAYS('01', CURRENT_DATE, 1, 'FCTEST') "result date" FROM
DUMMY;
输出结果：10.01.2014
6、 ADD_YEARS Function (Datetime)
计算指定日期加指定年份。
语法：ADD_YEARS (
高品质SAP技术群：455101474 matinal
举例：SELECT ADD_YEARS (TO_DATE (CURRENT_DATE, 'YYYY-MM-DD'), 1) "add years" FROM
DUMMY;
7、 CURRENT_DATE Function (Datetime)
返回当前本地系统日期。
语法：CURRENT_DATE
举例：SELECT CURRENT_DATE "current date" FROM DUMMY;
8、 CURRENT_TIME Function (Datetime)
返回本地系统时间。
语法：CURRENT_TIME
举例：SELECT CURRENT_TIME "current time" FROM DUMMY;
9、 CURRENT_TIMESTAMP Function (Datetime)
返回当前本地系统时间戳信息。
语法：CURRENT_TIMESTAMP
举例：SELECT CURRENT_TIMESTAMP "current timestamp" FROM DUMMY;
10、CURRENT_UTCDATE Function (Datetime)
返回当前的UTC日期。
语法：CURRENT_UTCDATE
举例：SELECT CURRENT_UTCDATE "Coordinated Universal Date" FROM DUMMY;
11、CURRENT_UTCTIME Function (Datetime)
返回当前UTC时间。
语法：CURRENT_UTCTIME
举例：SELECT CURRENT_UTCTIME "Coordinated Universal Time" FROM DUMMY;
12、CURRENT_UTCTIMESTAMP Function (Datetime)
返回当前UTC时间戳。
语法：CURRENT_UTCTIMESTAMP
举例：SELECT CURRENT_UTCTIMESTAMP "Coordinated Universal Timestamp" FROM
DUMMY;
13、DAYNAME Function (Datetime)
返回指定日期的工作日。
语法：DAYNAME (
举例：SELECT DAYNAME ('2011-05-30') "dayname" FROM DUMMY;
14、DAYOFMONTH Function (Datetime)
返回指定日期的月份中的某一天。
语法：DAYOFMONTH (
举例：SELECT DAYOFMONTH ('2011-05-30') "dayofmonth" FROM DUMMY;
15、DAYOFYEAR Function (Datetime)
返回指定日期的年中某一天的整数表示形式。
语法：DAYOFYEAR (
举例：SELECT DAYOFYEAR ('2011-05-30') "dayofyear" FROM DUMMY;
16、DAYS_BETWEEN Function (Datetime)
计算d1和d2之间的天数。
语法：DAYS_BETWEEN (
举例：SELECT DAYS_BETWEEN (TO_DATE ('2009-12-05', 'YYYY-MM-DD'),
TO_DATE('2010-01-05', 'YYYY-MM-DD')) "days between" FROM DUMMY;
高品质SAP技术群：455101474 matinal
17、EXTRACT Function (Datetime)
查找并返回指定日期的指定datetime字段的值。
语法：EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM
举例：SELECT EXTRACT (YEAR FROM TO_DATE ('2010-01-04', 'YYYY-MM-DD')) "extract"
FROM DUMMY;
18、HOUR Function (Datetime)
返回指定时间的小时的整数表示形式。
语法：HOUR (
举例：SELECT HOUR ('12:34:56') "hour" FROM DUMMY;
19、ISOWEEK Function (Datetime)
返回指定日期的ISO年份和周数。
语法：ISOWEEK (
举例：SELECT ISOWEEK (TO_DATE('2011-05-30', 'YYYY-MM-DD')) "isoweek" FROM DUMMY;
20、LAST_DAY Function (Datetime)
返回包含指定日期的月份的最后一天的日期。
语法：LAST_DAY (
举例：SELECT LAST_DAY (TO_DATE('2010-01-04', 'YYYY-MM-DD')) "last day" FROM DUMMY;
21、LOCALTOUTC Function (Datetime)
保存UTC和本地时间之间的转换时间的时间戳参数。
语法：LOCALTOUTC (
举例：SELECT LOCALTOUTC (TO_TIMESTAMP('2012-01-01 01:00:00', 'YYYY-MM-DD
HH24:MI:SS'), 'EST') "localtoutc" FROM DUMMY;
22、MINUTE Function (Datetime)
返回指定时间的分钟的整数表示。
语法：MINUTE (
举例：SELECT MINUTE ('12:34:56') "minute" FROM DUMMY;
23、MONTH Function (Datetime)
返回指定日期的月份数。
语法：MONTH(
举例：SELECT MONTH ('2011-05-30') "month" FROM DUMMY;
24、MONTHNAME Function (Datetime)
返回指定日期的月份名称。
语法：MONTHNAME(
举例：SELECT MONTHNAME ('2011-05-30') "monthname" FROM DUMMY;
25、MONTHS_BETWEEN Function (Datetime)
计算两个日期之间的月数。
语法：MONTHs_BETWEEN (
举例：SELECT MONTHS_BETWEEN(TO_DATE ('2003-01-01'), TO_DATE('2003-03-14'))
"months_between" FROM DUMMY;
26、NANO100_BETWEEN Function (Datetime)
计算两个日期之间的时间差。
语法：NANO100_BETWEEN (
举例：SELECT NANO100_BETWEEN ('2013-01-30', '2013-01-31') "nano100 between" FROM
DUMMY;
高品质SAP技术群：455101474 matinal
27、NEXT_DAY Function (Datetime)
返回指定日期后的第二天的日期。
语法：NEXT_DAY (
举例：SELECT NEXT_DAY (TO_DATE ('2009-12-31', 'YYYY-MM-DD')) "next day" FROM
DUMMY;
28、NOW Function (Datetime)
返回当前时间戳。
语法：NOW ()
举例：SELECT NOW () "now" FROM DUMMY;
29、QUARTER Function (Datetime)
语法：QUARTER (
举例：SELECT QUARTER (TO_DATE('2012-01-01', 'YYYY-MM-DD'), 2) "quarter" FROM
DUMMY;
30、SECOND Function (Datetime)
返回给定时间的秒的值。
语法：SECOND (
举例：SELECT SECOND ('2014-03-25 12:34:56.789') "subseconds" FROM DUMMY;
31、SECONDS_BETWEEN Function (Datetime)
计算两个指定日期之间的秒数。
语法：SECONDS_BETWEEN (
举例：SELECT SECONDS_BETWEEN ('2009-12-05', '2010-01-05') "seconds between" FROM
DUMMY;
32、UTCTOLOCAL Function (Datetime)
将UTC和本地时间之间的指定时间戳转换。
语法：UTCTOLOCAL (
举例：SELECT UTCTOLOCAL (TO_TIMESTAMP('2012-01-01 01:00:00', 'YYYY-MM-DD
HH24:MI:SS'), 'EST', 'sap') "utctolocal" FROM DUMMY;
33、WEEK Function (Datetime)
返回指定日期的星期编号。
语法：WEEK (
举例：SELECT WEEK (TO_DATE('2011-05-30', 'YYYY-MM-DD')) "week" FROM DUMMY;
34、WEEKDAY Function (Datetime)
返回指定日期的星期几。
语法：WEEKDAY (
举例：SELECT WEEKDAY (TO_DATE ('2010-12-31', 'YYYY-MM-DD')) "week day" FROM
DUMMY;
35、WORKDAYS_BETWEEN Function (Datetime)
计算指定开始日期和指定结束日期之间的工作日数。
语法：WORKDAYS_BETWEEN (
TFACS bitfield
0
1
1
2
Day of the month Reason for not working
Public Holiday
高品质SAP技术群：455101474 matinal
TFACS bitfield
1
0
0
0
1
1
1
1
0
0
1
1
1
1
1
0
0
1
1
1
1
1
0
0
1
1
1
1
1
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Day of the month
Weekend
Weekend
Reason for not working
Public Holiday
Weekend
Weekend
Weekend
Weekend
Weekend
Weekend
高品质SAP技术群：455101474 matinal
举例：SELECT WORKDAYS_BETWEEN('01', '2014-01-09', '2014-01-10' , 'FCTEST') "workdays"
FROM DUMMY;
36、YEAR Function (Datetime)
返回指定日期的年份。
语法：YEAR (
举例：SELECT YEAR (TO_DATE ('2011-05-30', 'YYYY-MM-DD')) "year" FROM DUMMY;
37、YEARS_BETWEEN Function (Datetime)
计算两个指定日期之间的年数。
语法：YEARS_BETWEEN (
举例：SELECT YEARS_BETWEEN(TO_DATE('2001-01-01'), TO_DATE('2003-03-14'))
"years_between" FROM DUMMY;
SELECT YEARS_BETWEEN(TO_DATE('2003-10-03'), TO_DATE('2001-01-14')) "years_between"
FROM DUMMY;
SELECT YEARS_BETWEEN(TO_DATE('2001-10-15'), TO_DATE('2003-01-14')) "years_between"
FROM DUMMY;
SELECT YEARS_BETWEEN(TO_DATE('2001-10-13'), TO_DATE('2003-01-14')) "years_between"
FROM DUMMY;
发布评论