I want to fetch a date column with the 'DD-MMM-Y

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

(Continued from previous question...)

I want to fetch a date column with the 'DD-MMM-YYYY' format. Can I do it in a single query?

Q: I want to fetch a date column with the 'DD-MMM-YYYY' format. Can I do it in a single query? I tried to run the following SQL, but got an error:

     SELECT  CHAR(T1.COL_DT ,'DD-MMM-YYYY'),
     FROM      TABLE1 T1;         

Any ideas?

A: You are not using the CHAR function correctly. When using CHAR to convert a date column into a character representation, the second parameter can only be one of the following: ISO, USA, EUR, JIS, or LOCAL. Consult Table 1 for the format to which each of these corresponds.

To return the data in the format you are requesting, DD-MMM-YYYY, you will need to install a local date format routine. Get in touch with your system programmer to discuss whether this is feasible and how to do it.

(Continued on next question...)

Other Job Interview Questions

• 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 to enter date in format mm-dd-yyyy hh:mm:Ss in DB2. How can I do that?
• 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 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?
• 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 to fetch a date column with the 'DD-MMM-YYYY' format. Can I do it in a single query?
• 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...