I have Toad for DB2 ver 5.0.4 and am having a problem when I try and export dates/time stamps to Excel. My query will show a time stamp of 2/26/2013 6:02:06 PM, but when it exports to Excel I see 2013-02-26-18.02.06.000000 . The cells in Excel are then frozen I can not Format then to another Date style. Any suggestions?
Many users require that the exported format keeps all the precision of timestamp column values. This is needed especially when they use the timestamp column as a unique key and rely on the date, time and microseconds for uniqueness. Users can use Toad and export and then re-import values and we will keep the uniqueness.
If we only formated those as ‘2/26/2013 6:02:06 PM’ the microseconds and uniqueness could be lost.
I am not sure what you are looking to do with the value, however if you just want the date or just want the time, you could modify your query to cast to return just those such as:
SELECT date(COL_TIMESTAMP) as DATE, time(COL_TIMESTAMP) as TIME…
Hope this helps,
Jeff
Jeff, Thanks for the reply.Your solution will work if I just wanted to convert the timestamp to just a date or just a time. I would then need to have date and time in separate columns. I was looking to just have the time stamp, i.e. 2/26/2013 6:02:06 PM, to export to Excel in the same format. Can I force the format to only be mm/dd/yyy hh:mm:ss AM/PM?
To get one column you could try the VARCHAR_FORMAT function like:
SELECT VARCHAR_FORMAT(TS,‘MM/DD/YYYY HH:MI:SS AM’) AS DATE_TIME FROM TEST_TS;
– The above works for DB2 z/OS v10 or DB2 LUW v10)
– If you have v9.x of DB2 you might want to try:
SELECT VARCHAR_FORMAT(TS,‘MM/DD/YYYY HH:MI:SS’) AS DATE_TIME FROM TEST_TS;
Or you could select the values in the Grid in Toad, right click and select Copy Cells.
Then paste those into a Excel worksheet.
HI, I’m struggling to extract the time from ‘MM/DD/YYYY HH:MI:SS AM’, I have limited experience with this date format within SQL (although extracted the time via sas), would you be able to advise how to do this,
Kind regards
