SQL Dialects Reference/Functions and expressions/Date and time functions

Date and time functions SQL Dialects Reference/Data structure definition/Data types/Date and time types Date and time types lang_datefunc.html F051-06 Current date CURRENT_DATE r0005870.htm CURRENT DATECURRENT_DATE CURRENT_DATE date-and-time-functions.html#function_curdate CURDATE()CURRENT_DATECURRENT_DATE() CURRENT_DATE CURRENT_DATE functions-datetime.html#FUNCTIONS-DATETIME-CURRENT CURRENT_DATE CURDATE TRUNC(CURRENT_DATE), TRUNC(SYSDATE) functions036.htm CAST(GETDATE() AS DATE)CONVERT(DATE, GETDATE()) SYSDATE, CURRENT_TIMESTAMP, UNIX_TIMESTAMP (incl time also) DATE('TODAY') F051-01 Current time CURRENT_TIME r0005885.htm CURRENT TIMECURRENT_TIME CURRENT_TIME CURTIME, CURRENT_TIME CURRENT_TIME CURRENT_TIME CURRENT_TIME CURTIME TIME(DATE('NOW')) F051-01 Current date and time CURRENT_TIMESTAMP r0005886.htm CURRENT TIMESTAMPCURRENT_TIMESTAMP CURRENT_TIMESTAMP NOW(), CURRENT_TIMESTAMP NOW(), CURRENT_TIMESTAMP NOW(), CURRENT_TIMESTAMP CURRENT_TIMESTAMP, 'NOW' NOW CURRENT_DATE, SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP GETDATE(), CURRENT_TIMESTAMP SYSDATE, CURRENT_TIMESTAMP, UNIX_TIMESTAMP DATE('NOW') Add interval to date date + arg date + arg DATE_ADD date-and-time-functions.html#function_date-add arg1 + arg2 arg1 + arg2 DATEADD arg1 + interval in days or fractional days arg1 + interval in days or fractional days ADD_MONTHS() see more arg1 + interval in days or fractional days DATEADD arg1 + arg2ADD_MONTHS arg1 + arg2arg1 = date, arg2 = time-interval;date+'15 day' Subtract interval from date date - arg date - arg DATE_SUB arg1 - interval '1' DAY arg1 - arg2 arg1 - arg2 DATEDIFF arg1 - arg2 (sysdate-interval '1' MONTH) arg1 - interval in days or fractional days DATEDIFF arg1 - arg2 arg1 - arg2arg1 = date, arg2 = time-interval;date-'3 month' Date difference (date1 - date2) field date1 - date2 DATEDIFF AGE arg1 - arg2DATEDIFF CAST(arg1 - arg2 as bigint)/1000 (diff in seconds) DATEDIFF arg1 - arg2 see more see more-2 DATEDIFF arg1 - arg2 date1 - date2;interval( 'day', date1 - date2) Last day of month date + 1 MONTH - DAY(date) DAYS LAST_DAY LASTDAYMONTH LAST_DAY LAST_DAY LAST_DAY Time zone conversion date AT TIME ZONE offset CONVERT_TZ TIMEZONE TIMEZONE NEW_TIME First weekday after date NEXT_DAY NEXT_DAY F051-04 Convert date to string CAST (x AS STRING) TO_CHAR(value, format)VARCHAR_FORMAT(value, format) STRFTIME(format, value) DATE_FORMAT(value, format) CAST (x AS STRING) TO_CHAR(value, format) CAST(value, datetype)DATETOSTR CAST TO_CHAR DATENAME TO_CHAR(value, format) DATE_FORMAT(date, format)char(date) F051-04 Convert date to number INT(date) DATE_PART EXTRACT CAST TO_NUMBER(TO_CHAR()) DATEPART TO_NUMBER(value)DATESPLIT F051-04 Convert string to date CAST (x AS DATE) DATE(value)TIMESTAMP(value) CAST, TIMESTAMP 'yyyy-mm-dd HH:mm:ss' TO_DATE CAST STRINGDATE TO_DATE CAST TO_DATE(value, format)TO_TIMESTAMP(value, format) DATE(string) Extract year from DATE or DATETIME x EXTRACT(YEAR FROM x) strftime('%Y', x) date-and-time-functions.html#function_year YEAR(x)date-and-time-functions.html#function_extract EXTRACT(YEAR FROM x) EXTRACT(YEAR FROM x) EXTRACT(YEAR FROM x) functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT EXTRACT(YEAR FROM x) functions050.htm 186313 YEAR(x)174420 DATEPART(year, x) SQL%20Reference%20Guide/datefunctions2.htm YEAR(x)SQL%20Reference%20Guide/datefunctionsforingresdatedatatype.htm#o3679 DATE_PART(YEAR, x)These functions are only applicable to ingresdate data type in Ingres; normal dates, represented by ansidate datatype should be converted to ingresdate first. EXTRACT(YEAR FROM x) Extract month from DATE or DATETIME x EXTRACT(MONTH FROM x) strftime('%m', x) date-and-time-functions.html#function_month MONTH(x)date-and-time-functions.html#function_extract EXTRACT(MONTH FROM x) EXTRACT(MONTH FROM x) EXTRACT(MONTH FROM x) functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT EXTRACT(MONTH FROM x) functions050.htm 187813 MONTH(x)174420 DATEPART(month, x) SQL%20Reference%20Guide/datefunctions2.htm MONTH(x)SQL%20Reference%20Guide/datefunctionsforingresdatedatatype.htm#o3679 DATE_PART(MONTH, x) EXTRACT(MONTH FROM x) Extract day of month from DATE or DATETIME x EXTRACT(DAY FROM x) strftime('%d', x) date-and-time-functions.html#function_dayofmonth DAYOFMONTH(x)date-and-time-functions.html#function_day DAY(x)date-and-time-functions.html#function_extract EXTRACT(DAY FROM x) EXTRACT(DAY FROM x) EXTRACT(DAY FROM x) functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT EXTRACT(DAY FROM x) functions050.htm 176052 DAY(x)174420 DATEPART(day, x) SQL%20Reference%20Guide/datefunctions2.htm DAY(x)SQL%20Reference%20Guide/datefunctionsforingresdatedatatype.htm#o3679 DATE_PART(DAY, x) EXTRACT(DAY FROM x) Extract hour (0…23) from TIME or DATETIME x EXTRACT(HOUR FROM x) strftime('%H', x) date-and-time-functions.html#function_hour HOUR(x)date-and-time-functions.html#function_extract EXTRACT(HOUR FROM x) EXTRACT(HOUR FROM x) EXTRACT(HOUR FROM x) functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT EXTRACT(HOUR FROM x) functions050.htm DATEPART(hour, x) 174420 SQL%20Reference%20Guide/datefunctions2.htm HOUR(x)SQL%20Reference%20Guide/datefunctionsforingresdatedatatype.htm#o3679 DATE_PART(HOUR, x) EXTRACT(HOUR FROM x) |}