Interview Questions

I need to convert my date, time, and timestamp column values into their separate components. What facilities does DB2 provide to allow me to do this?

DB2's Dates questions and answers

(Continued from previous question...)

I need to convert my date, time, and timestamp column values into their separate components. What facilities does DB2 provide to allow me to do this?

A: DB2 provides a vast number of functions that can be applied to DATE, TIME, and TIMESTAMP columns to help you. Chances are you will find a function to help you with your task at hand, depending on your specific needs. Consider the following functions:

CHAR Converts a DB2 date, time, timestamp, ROWID, floating point, integer, or decimal value to a character value.
CONCAT Converts two strings into the concatenation of the two strings.
DATE Converts a value representing a date to a DB2 date. The value to be converted can be a DB2 timestamp, a DB2 date, a positive integer, or a character string.
DAY Returns the day portion of a DB2 date or timestamp.
DAYOFMONTH Similar to DAY except DAYOFMONTH can not accept a date duration or time duration as an argument.
DAYOFWEEK Converts a date, timestamp, or string representation of a date or timestamp into an integer that represents the day of the week. The value 1 represents Sunday, 2 Monday, 3 Tuesday, 4 Wednesday, 5 Thursday, 6 Friday, and 7 Saturday.
DAYOFYEAR Converts a date, timestamp, or string representation of a date or timestamp into an integer that represents the day within the year. The value 1 represents January 1st, 2 January 2nd, and so on.
DAYS Converts a DB2 date or timestamp into an integer value representing one more than the number of days since January 1, 0001.
HOUR Returns the hour portion of a time, a timestamp, or a duration.
JULIAN_DAY Converts a DB2 date or timestamp, or character representation of a date or timestamp, into an integer value representing the number of days from January 1, 4712 BC to the date specified in the argument.
LTRIM Removes the leading blanks from a character string.
MICROSECOND Returns the microsecond component of a timestamp or the character representation of a timestamp.
MIDNIGHT_SECONDS Returns the number of seconds since midnight for the specified argument which must be a time, timestamp, or character representation of a time or timestamp.
MINUTE Returns the minute portion of a time, a timestamp, a character representation of a time or timestamp, or a duration.
MONTH Returns the month portion of a date, a timestamp, a character representation of a date or timestamp, or a duration.
QUARTER Converts a date, timestamp, or string representation of a date or timestamp into an integer that represents the quarter within the year. The value 1 represents first quarter, 2 second quarter, 3 third quarter, and 4 fourth quarter.
RTRIM Removes the trailing blanks from a character string.
SECOND Returns the seconds portion of a time, a timestamp, a character representation of a time or timestamp, or a duration.
STRIP Removes leading, trailing, or both leading and trailing blanks (or any specific character) from a string expression.
TAN Returns the tangent of the argument as an angle expressed in radians.
TIME Converts a value representing a valid time to a DB2 time. The value to be converted can be a DB2 timestamp, a DB2 time, or a character string.
TIMESTAMP Obtains a timestamp from another timestamp, a valid character-string representation of a timestamp, or a combination of date and time values.
WEEK Converts a date, timestamp, or string representation of a date or timestamp into an integer that represents the week within the year (with Sunday as the first day of the week). The value 1 represents the first week of the year, 2 the second week, and so on.
WEEK_ISO Converts a date, timestamp, or string representation of a date or timestamp into an integer that represents the week within the year (with Monday as the first day of the week).
YEAR Returns the year portion of a date, a timestamp, or a duration.

Other Interview Questions