DB2, Halloween, and a Time Saver

A roadblock?

Well, right now I am busy with all kinds of things, juggling different database and cloud topics. Last weekend, Europe has switched back from Daylight Saving Time or Summer time to standard time, North America will follow this weekend. During this process, supposedly, you get one additional hour of sleep or time for other stuff. In my case it turned out that I spent the hour changing wall clocks, alarm clocks and watches. Anyway, here is a real time saver for DB2...

DB2 has the function TIMESTAMP_FORMAT (with synomy TO_DATE and TO_TIMESTAMP) to convert string values to timestamps or dates. The expected date and time format can be specified via the usual placeholders, e.g., MM for months or SS for seconds. However, there are some tricky parts to it and I am going to treat you with a solution (this was the "trick & treat" Halloween reference...):

db2 "values to_date('30.10.2015 00:00:00','DD.MM.YYYY HH:MI:SS')"
SQL20448N  "31.10.2015 00:00:00" cannot be interpreted using format string
"DD.MM.YYYY HH:MI:SS" for the TIMESTAMP_FORMAT function.  SQLSTATE=22007

db2 "values timestamp_format('30.10.2015 24:00:00','DD.MM.YYYY HH:MI:SS')"
SQL20448N  "31.10.2015 24:00:00" cannot be interpreted using format string
"DD.MM.YYYY HH:MI:SS" for the TIMESTAMP_FORMAT function.  SQLSTATE=22007

In both cases I am trying to convert midnight on October 31st to an internal timestamp value. Both calls return an error. The reason is that "HH" is the same as "HH12", the 12-hour format. Using "HH24" (24-hour format) everything is ok:

db2 "values timestamp_format('31.10.2015 00:00:00','DD.MM.YYYY HH24:MI:SS')"

1                        
--------------------------
2015-10-31-00.00.00.000000

  1 record(s) selected.

db2 "values timestamp_format('31.10.2015 24:00:00','DD.MM.YYYY HH24:MI:SS')"

1                        
--------------------------
2015-10-31-24.00.00.000000

  1 record(s) selected.

If you want to use the HH/HH12 format, you need to specify where to expect the so-called Meridian indicator (AM/PM):

db2 "values timestamp_format('31.10.2015 12:00:00am','DD.MM.YYYY HH:MI:SSAM')"

1                        
--------------------------
2015-10-31-00.00.00.000000

  1 record(s) selected.

db2 "values timestamp_format('31.10.2015 12:00:00am','DD.MM.YYYY HH:MI:SSPM')"

1                        
--------------------------
2015-10-31-00.00.00.000000

  1 record(s) selected.

That's all, enjoy the weekend and Halloween...