This section introduces you to some common Db2 date functions that help you manipulate date and time data effectively.
Function | Description |
---|---|
ADD_DAYS | Returns a datetime value that represents the first argument plus a specified number of days. |
ADD_HOURS | Returns a timestamp value that represents the first argument plus a specified number of hours. |
ADD_MINUTES | Returns a timestamp value that represents the first argument plus a specified number of minutes. |
ADD_MONTHS | Returns a datetime value that represents expression plus a specified number of months. |
ADD_SECONDS | Returns a timestamp value that represents the first argument plus a specified number of seconds and fractional seconds. |
ADD_YEARS | Returns a datetime value that represents the first argument plus a specified number of years. |
AGE | Returns a numeric value that specifies the number of full years, full months, and full days between the current timestamp and the argument. |
DATE_PART | Returns portion of a datetime based on its argument. |
DATE_TRUNC | Returns a timestamp expression rounded to the specified unit. |
DAY | Returns the day part of a value. |
DAYNAME | Returns a character string containing the name of the day (for example, Friday) for the day portion of expression, based on locale-name or the value of the special register CURRENT LOCALE LC_TIME. |
DAYOFMONTH | Returns an integer between 1 and 31 that represents the day of the month. |
DAYOFWEEK | Returns the day of the week in the first argument as an integer value. The integer value is in the range 1-7, where 1 represents the first day of the week, as specified in the second argument. |
DAYOFWEEK_ISO | Returns the day of the week from a value, where 1 is Monday and 7 is Sunday. |
DAYOFYEAR | Returns the day of the year from a value. |
DAYS | Returns an integer representation of a date. |
DAYS_BETWEEN | Returns the number of full days between the specified arguments. |
DAYS_TO_END_OF_MONTH | Returns the number of days to the end of the month. |
EXTRACT | Returns a portion of a date or timestamp based on the arguments. |
FIRST_DAY | Returns a date or timestamp that represents the first day of the month of the argument. |
FROM_UTC_TIMESTAMP | Returns a TIMESTAMP that is converted from Coordinated Universal Time to the timezone that is specified by the timezone string. |
HOUR | Returns the hour part of a value. |
HOURS_BETWEEN | Returns the number of full hours between the specified arguments. |
JULIAN_DAY | Returns an integer value representing the number of days from January 1, 4712 B.C. to the date specified in the argument. |
LAST_DAY | Returns a datetime value that represents the last day of the month of the argument. |
MICROSECOND | Returns the microsecond part of a value. |
MIDNIGHT_SECONDS | Returns an integer value representing the number of seconds between midnight and a specified time value. |
MINUTE | Returns the minute part of a value. |
MINUTES_BETWEEN | Returns the number of full minutes between the specified arguments. |
MONTH | Returns the month part of a value. |
MONTHNAME | Returns a character string containing the name of the month (for example, January) for the month portion of expression, based on locale-name or the value of the special register CURRENT LOCALE LC_TIME. |
MONTHS_BETWEEN | Returns an estimate of the number of months between expression1 and expression2. |
NEXT_DAY | Returns a datetime value that represents the first weekday, named by string-expression, that is later than the date inexpression. |
NEXT_MONTH | Returns the first day of the next month after the specified date. |
NEXT_QUARTER | Returns the first day of the next quarter after the specified date. |
NEXT_WEEK | Returns the first day of the next week after the specified date. |
NEXT_YEAR | Returns the first day of the next year after the specified date. |
NOW | Returns a timestamp based on when the SQL statement is executed at the current server. |
QUARTER | Returns an integer that represents the quarter of the year in which a date resides. |
ROUND | Returns a datetime value, rounded to the unit specified by format-string. |
ROUND_TIMESTAMP | Returns a timestamp that is the expression rounded to the unit specified by the format-string. |
SECOND | Returns the seconds part of a value. |
SECONDS_BETWEEN | Returns the number of full seconds between the specified arguments. |
THIS_MONTH | Returns the first day of the month in the specified date. |
THIS_QUARTER | Returns the first day of the quarter in the specified date. |
THIS_WEEK | Returns the first day of the week in the specified date. |
THIS_YEAR | Returns the first day of the year in the specified date. |
TIMESTAMP_FORMAT | Returns a timestamp from a character string (argument1) that has been interpreted using a format template (argument2). |
TIMESTAMP_ISO | Returns a timestamp value based on a date, time, or timestamp argument. If the argument is a date, it inserts zero for all the time elements. If the argument is a time, it inserts the value of CURRENT DATE for the date elements, and zero for the fractional time element. |
TIMESTAMPDIFF | Returns an estimated number of intervals of type argument1, based on the difference between two timestamps. The second argument is the result of subtracting two timestamp types and converting the result to CHAR. |
TO_CHAR | Returns a CHARACTER representation of a timestamp. |
TO_DATE | Returns a timestamp from a character string. |
TO_NCHAR | Returns a national character representation of an input expression that has been formatted using a character template. |
TO_TIMESTAMP | Returns a timestamp that is based on the interpretation of the input string using the specified format. |
TO_UTC_TIMESTAMP | Returns a TIMESTAMP that is converted from Coordinated Universal Time to the timezone specified by the timezone string. |
TRUNCATE or TRUNC | Returns a datetime value, truncated to the unit specified by format-string. |
TRUNC_TIMESTAMP | Returns a timestamp that is the expression truncated to the unit specified by the format-string. |
VARCHAR_FORMAT | Returns a CHARACTER representation of a timestamp (argument1), formatted according to a template (argument2). |
WEEK | Returns the week of the year from a value, where the week starts with Sunday. |
WEEK_ISO | Returns the week of the year from a value, where the week starts with Monday. |
WEEKS_BETWEEN | Returns the number of full weeks between the specified arguments. |
YEAR | Returns the year part of a value. |
YEARS_BETWEEN | Returns the number of full years between the specified arguments. |
YMD_BETWEEN | Returns a numeric value that specifies the number of full years, full months, and full days between two datetime values. |
Was this tutorial helpful ?