I need to convert my date, time, and timestamp c

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(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 Job Interview Questions

• I have a DATE column in a DB2 table, but I do not want it to display the way DB2 displays it by default. How can I get a date format retrieved from a column in a table from DB2 database in the format MM/DD/YYYY?
• My format does not fit into any of these listed in question 1. What if I have a DATE stored like YYYYMMDD (with no dashes or slashes) and I want to compare it to a DB2 date?
• I want to fetch a date column with the 'DD-MMM-YYYY' format. Can I do it in a single query?
• I'm confused about how DB2 handles date arithmetic! It seems that I can subtract a date from a date, or a time from a time, but what will DB2 return as the result of such a calculation? Please help?
• Is there a way in DB2 date arithmetic to express the duration resulting from date subtraction, as a total-number-of-days (exact total, and not an approximate total)?
• I want subtract a variable from the current date. Depending upon certain criteria ...
• I have a DATE column in a DB2 table, but I do not want it to display the way DB2 displays it by default. How can I get a date format retrieved from a column in a table from DB2 database in the format MM/DD/YYYY?
• I want subtract a variable from the current date. Depending upon certain criteria ...
• I want to fetch a date column with the 'DD-MMM-YYYY' format. Can I do it in a single query?
• Is there a way in DB2 date arithmetic to express the duration resulting from date subtraction, as a total-number-of-days (exact total, and not an approximate total)?
• More...