HANA常用函数
https://help.sap.com/viewer/f1b440ded6144a54ada97ff95dac7adf/2.6/en-US/66a4169b84b2466892e1af9781049836.html
SAP Help Portal
帮助文档
获取日期时间
- SELECT CURRENT_TIMESTAMP FROM DUMMY ;
2020-09-24 17:55:33
2.SELECT TO_DATE(CURRENT_TIMESTAMP) AS DATE, TO_TIME(CURRENT_TIMESTAMP) AS TIME FROM DUMMY;
DATE TIME
2020-09-24 18:02:19
3.SELECT CAST((TO_DATE(CURRENT_TIMESTAMP)) AS VARCHAR(10)) AS DATE
, CAST((TO_TIME(CURRENT_TIMESTAMP)) AS VARCHAR(10)) AS TIME
FROM DUMMY;
DATE TIME
2022-03-10 16:26:26
4.SELECT
REPLACE(CAST((TO_DATE(CURRENT_TIMESTAMP)) AS VARCHAR(10)),’-’,’’) AS DATE
, REPLACE(CAST((TO_TIME(CURRENT_TIMESTAMP)) AS VARCHAR(8)),’:’,’’) AS TIME
FROM DUMMY
DATE TIME
20200924 181021
5.拼接字符串
SELECT
CONCAT(REPLACE(CAST((TO_DATE(CURRENT_TIMESTAMP)) AS VARCHAR(10)),’-’,’’),
REPLACE(CAST((TO_TIME(CURRENT_TIMESTAMP)) AS VARCHAR(10)),’:’,’’)) AS DATETIME
FROM DUMMY
DATETIME
20200924182020
日期处理
ADD_DAYS
语法:
ADD_DAYS (d, n)
描述:计算日期d 后n 天的值。
例子:
SELECT ADD_DAYS (TO_DATE (‘2009-12-05’, ‘YYYY-MM-DD’), 30) “add days” FROM DUMMY;
add days
2010-01-04
ADD_MONTHS
语法:
ADD_MONTHS (d, n)
描述:
计算日期d 后n 月的值。
例子:
SELECT ADD_MONTHS (TO_DATE (‘2009-12-05’, ‘YYYY-MM-DD’), 1) “add months” FROM DUMMY
;
add months
2010-01-05
ADD_SECONDS
语法:
ADD_SECONDS (t, n)
描述:
计算时间t 后n 秒的值。
例子:
SELECT ADD_SECONDS (TO_TIMESTAMP (‘2012-01-01 23:30:45’), 60*30) “add seconds” FROM
DUMMY;
add seconds
2012-01-02 00:00:45.0
ADD_YEARS
语法:
ADD_YEARS (d, n)
描述:
计算日期d 后n 年的值。
例子:
SELECT ADD_YEARS (TO_DATE (‘2009-12-05’, ‘YYYY-MM-DD’), 1) “add years” FROM DUMMY;
add years
2010-12-05
CURRENT_DATE
语法:
CURRENT_DATE
描述:
返回当前本地系统日期。
例子:
SELECT CURRENT_DATE “current date” FROM DUMMY;
current date
Nov 19, 2015
CURRENT_TIME
语法:
CURRENT_TIME
描述:
返回当前本地系统时间。
例子:
SELECT CURRENT_TIME “current time” FROM DUMMY;
current time
9:52:13 AM
CURRENT_TIMESTAMP
语法:
CURRENT_TIMESTAMP
描述:
返回当前本地系统的时间戳信息。
例子:
SELECT CURRENT_TIMESTAMP “current timestamp” FROM DUMMY;
current timestamp
Nov 19, 2015 9:52:25.258 AM
CURRECT_UTCDATE
语法:
CURRENT_UTCDATE
描述:
返回当前UTC 日期。UTC 代表协调世界时,也被称为格林尼治标准时间(GMT)。
例子:
SELECT CURRENT_UTCDATE “Coordinated Universal Date” FROM DUMMY;
Coordinated Universal Time
Nov 19, 2015
CURRENT_UTCTIME
语法:
CURRENT_UTCTIME
描述:
返回当前UTC 时间。
例子:
SELECT CURRENT_UTCTIME “Coordinated Universal Time” FROM DUMMY;
Coordinated Universal Time
1:52:55 AM
CURRENT_UTCTIMESTAMP
语法:
CURRENT_UTCTIMESTAMP
描述:
返回当前UTC 时间戳。
例子:
SELECT CURRENT_UTCTIMESTAMP “Coordinated Universal Timestamp” FROM DUMMY;
Coordinated Universal Timestamp
Nov 19, 2015 1:53:12.533 AM
DAYNAME
语法:
DAYNAME (d)
描述:
返回一周中日期d 的英文名。
例子:
SELECT DAYNAME (‘2011-05-30’) “dayname” FROM DUMMY;
dayname
MONDAY
DAYOFMONTH
语法:
DAYOFMONTH (d)
描述:
返回一个月中日期d 的整数数字。
例子:
SELECT DAYOFMONTH (‘2011-05-30’) “dayofmonth” FROM DUMMY;
dayofmonth
30
DAYOFYEAR
语法:
DAYOFYEAR (d)
描述:
返回一年中代表日期d 的整数数字。
例子:
SELECT DAYOFYEAR (‘2011-05-30’) “dayofyear” FROM DUMMY;
dayofyear
150
DAYS_BETWEEN
语法:
DAYS_BETWEEN (d1, d2)
描述:
计算d1 和d2 之间的日期数。
例子:
SELECT DAYS_BETWEEN (TO_DATE (‘2009-12-05’, ‘YYYY-MM-DD’), TO_DATE(‘2010-01-05’, ‘YYYY-MMDD’))
“days between” FROM DUMMY;
days between
31
EXTRACT
语法:
EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM d)
描述:
返回日期d 中指定的时间日期字段的值。
例子:
SELECT EXTRACT (YEAR FROM TO_DATE (‘2011-11-19’, ‘YYYY-MM-DD’)) “year”,
EXTRACT (MONTH FROM TO_DATE (‘2011-11-19’, ‘YYYY-MM-DD’)) “month”,
EXTRACT (DAY FROM TO_DATE (‘2011-11-19’, ‘YYYY-MM-DD’)) “day” FROM DUMMY;
year;month;day
2011;11;19
HOUR
语法:
HOUR (t)
描述:
返回时间t 中表示小时的整数。
例子:
SELECT HOUR (‘12:34:56’) “hour” FROM DUMMY;
hour
12
ISOWEEK
语法:
ISOWEEK (d)
描述:
返回日期d 的ISO 年份和星期数。星期数前缀为字母W。另请阅WEEK。
例子:
SELECT ISOWEEK (TO_DATE(‘2011-05-30’, ‘YYYY-MM-DD’)) “isoweek” FROM DUMMY;
Isoweek
2011-W22
LAST_DAY
语法:
LAST_DAY (d)
描述:
返回包含日期d 的月的最后一天日期。
例子:
SELECT LAST_DAY (TO_DATE(‘2010-01-04’, ‘YYYY-MM-DD’)) “last day” FROM DUMMY;
last day
2010-01-31
LOCALTOUTC
语法:
LOCALTOUTC (t, timezone)
描述:
将timezone 下的本地时间t 转换为UTC 时间。
例子:
SELECT LOCALTOUTC (TO_TIMESTAMP(‘2012-01-01 01:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), ’
EST’) “localtoutc” FROM DUMMY;
localtoutc
2012-01-01 06:00:00.0
MINUTE
语法:
MINUTE(t)
描述:
返回时间t 中表示分钟的数字。
例子:
SELECT MINUTE (‘12:34:56’) “minute” FROM DUMMY;
minute
34
MONTH
语法:
MONTH(d)
描述:
返回日期d 所在月份的数字。
例子:
SELECT MONTH (‘2011-05-30’) “month” FROM DUMMY;
month
5
MONTHNAME
语法:
MONTHNAME(d)
描述:
返回日期d 所在月份的英文名。
例子:
SELECT MONTHNAME (‘2011-05-30’) “monthname” FROM DUMMY;
monthname
MAY
NEXT_DAY
语法:
NEXT_DAY (d)
描述:
返回日期d 的第二天。
例子:
SELECT NEXT_DAY (TO_DATE (‘2009-12-31’, ‘YYYY-MM-DD’)) “next day” FROM DUMMY;
next day
2010-01-01
NOW
语法:
NOW ()
描述:
返回当前时间戳。
例子:
SELECT NOW () “now” FROM DUMMY;
now
2010-01-01 16:34:19.894
QUARTER
语法:
QUARTER (d, [, start_month ])
描述:
返回日期d 的年份,季度。第一季度由start_month 定义的月份开始,如果没有定义
start_month,第一季度假设为从一月开始。
例子:
SELECT QUARTER (TO_DATE(‘2012-01-01’, ‘YYYY-MM-DD’), 2) “quarter” FROM DUMMY;
quarter
2011-Q4
SECOND
语法:
SECOND (t)
描述:
返回时间t 表示的秒数。
例子:
SELECT SECOND (‘12:34:56’) “second” FROM DUMMY;
second
56
SECONDS_BETWEEN
语法:
SECONDS_BETWEEN (d1, d2)
描述:
计算日期参数d1 和d2 之间的秒数,语义上等同于d2-d1。
例子:
SELECT SECONDS_BETWEEN (‘2009-12-05’, ‘2010-01-05’) “seconds between” FROM DUMMY;
seconds between
2678400
UTCTOLOCAL
语法:
UTCTOLOCAL (t, timezone)
描述:
将UTC 时间值转换为时区timezone 下的本地时间。
例子:
SELECT UTCTOLOCAL (TO_TIMESTAMP(‘2012-01-01 01:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), ‘EST’)
“utctolocal” FROM DUMMY;
utctolocal
2011-12-31 20:00:00.0
WEEK
语法:
WEEK (d)
描述:
返回日期d 所在星期的整数数字。另请参阅ISOWEEK。
例子:
SELECT WEEK (TO_DATE(‘2011-05-30’, ‘YYYY-MM-DD’)) “week” FROM DUMMY;
week
23
WEEKDAY
语法:
WEEKDAY (d)
描述:
返回代表日期d 所在星期的日期数字。返回值范围为0 至6,表示Monday(0)至Sunday(6)。
例子:
SELECT WEEKDAY (TO_DATE (‘2010-12-31’, ‘YYYY-MM-DD’)) “week day” FROM DUMMY;
week day
4
YEAR
语法:
YEAR (d)
描述:
返回日期d 所在的年份数。
例子:
SELECT YEAR (TO_DATE (‘2011-05-30’, ‘YYYY-MM-DD’)) “year” FROM DUMMY;
year
2011
SAP HANA 常用函数
常用语句:
获取上年年份:SELECT TO_CHAR (current_date, ‘YYYY’)-1 “to char” FROM DUMMY
SAP HANA 常用函数:
数据类型转换函数(Data Type Conversion Functions ):
CAST 语法:CAST (表达式AS 数据类型) SELECT CAST (7 AS VARCHAR)“cast” FROM DUMMY; 结果----->7 把7转换成字符串。 把表达式转换成目标数据类型。
数据类型:TINYINT | SMALLINT | INTEGER | BIGINT | DECIMAL | SMALLDECIMAL | REAL | DOUBLE | ALPHANUM | VARCHAR | NVARCHAR | DAYDATE | DATE | TIME | SECONDDATE | TIMESTAMP
TO_ALPHANUM 语法: TO_ALPHANUM (value) SELECT TO_ALPHANUM (‘10’)“to alphanum” FROM DUMMY; 结果----> 10 把value转换成字母数字类型
TO_BIGINT 语法:TO_BIGINT (value) SELECT TO_BIGINT (‘10’)“to bigint” FROM DUMMY; 结果--------->10 把value转换成二进制类型
TO_BLOB 语法:
TO_CHAR SELECT TO_CHAR (TO_DATE(‘2009-12-31’), ‘YYYY/MM/DD’) “to char” FROM DUMMY;
TO_CLOB
TO_DATE SELECT TO_DATE(‘2010-01-12’, ‘YYYY-MM-DD’) “to date” FROM DUMMY;
TO_DATS
TO_DECIMAL
TO_DOUBLE
TO_INT
TO_INTEGER
TO_NCHAR
TO_NCLOB
TO_NVARCHAR
TO_REAL
TO_SECONDDATE
TO_SMALLDECIMAL
TO_SMALLINT
TO_TIME
TO_TIMESTAMP
TO_TINYINT
TO_VARCHAR
日期/时间函数(DateTime Functions ):
ADD_DAYS
ADD_MONTHS
ADD_SECONDS
ADD_YEARS
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_UTCDATE
CURRENT_UTCTIME
CURRENT_UTCTIMESTAMP
DAYNAME
DAYOFMONTH
DAYOFYEAR
DAYS_BETWEEN
EXTRACT
HOUR
ISOWEEK
LAST_DAY
LOCALTOUTC
MINUTE
MONTH
MONTHNAME
NEXT_DAY
NOW
QUARTER
SECOND
SECONDS_BETWEEN
UTCTOLOCAL
WEEK
WEEKDAY
YEAR
数字类型相关函数:(Number Functions)
ABS
ACOS
ASIN
ATAN
ATAN2
BINTOHEX
BITAND
CEIL
COS
COSH
COT
EXP
FLOOR
GREATEST
HEXTOBIN
LEAST
LN
LOG
MOD
POWER
ROUND
SIGN
SIN
SINH
SQRT
TAN
TANH
UMINUS
字符串相关函数:(String Functions)
ASCII SELECT ASCII(‘Ant’)“ascii” FROM DUMMY; 结果 ----> 65
CHAR SELECT CHAR (65) || CHAR (110) || CHAR (116)“character” FROM DUMMY;
CONCAT SELECT CONCAT (‘C’,‘at’) “concat” FROM DUMMY; 结果---->Cat CONCAT (str1, str2) 把字符串str1和str2拼接起来
LCASE SELECT LCASE (‘TesT’)“lcase” FROM DUMMY; 结果------>test 把字符串全部转换成小写
LEFT SELECT LEFT (‘Hello’, 3)“left” FROM DUMMY; 结果-------->Hel
LENGTH SELECT LENGTH (‘length in char’)“length” FROM DUMMY; 结果---->14 获取字符串的长度
LOCATE SELECT LOCATE (‘length in char’,‘char’) “locate” FROM DUMMY; 结果—>11 获取子字符串出现第一次的位置
LOWER SELECT LOWER (‘AnT’)“lower” FROM DUMMY; 结果----->ant 把字符串转成小写
NCHAR
REPLACE
RIGHT
RPAD
RTRIM
SUBSTR_AFTER
SUBSTR_BEFORE
SUBSTRING
TRIM
UCASE
UNICODE
UPPER
其他函数:
COALESCE
CURRENT_CONNECTION
CURRENT_SCHEMA
CURRENT_USER
GROUPING_ID
IFNULL
MAP
NULLIF
SESSION_CONTEXT
SESSION_USER
SYSUUID
文章来自于网络,如果侵犯了您的权益,请联系站长删除!