DB2 The syntax of the string representation of a datetime value is incorrect

Question

We have a staging table that's used to load raw data from our suppliers.

One column is used to capture a time-stamp but its data-type is varchar(265). Data's dirty: about 40% of the time, there is garbage data, otherwise time-stamp data like this

2011/11/15 20:58:48.041 

I have to create a report that filters some dates/timestamps out that column but where I try to cast it, I get an error:

db2 => select cast(loadedon as timestamp) from automation

1
--------------------------
SQL0180N  The syntax of the string representation of a datetime value is incorrect.  SQLSTATE=22007

What do I need to do in order to parse/cast the timestamp string?

Answer 1

The string format for a DB2 timestamp is either:

'2002-10-20-12.00.00.000000'

or

'2002-10-20 12:00:00'

You have to get your date string in either of these formats.

Also DB2 runs on a 24 hour clock even though the output sometimes uses a 12 hour clock (AM / PM)

So '2002-10-20 14:49:50' For 2:49:50 PM

Or '2002-10-20 00:00:00' For midnight. Output would be 12:00:00 AM