Bug using TO_CHAR to Convert Time to 12 Hour Format?

[becky]

Hi all,

Am I doing something wrong with converting a date to string when I want the hours to in 12 hour and not 24 hour format?

Code: Select all

dbadmin=> select sysdate, to_char(sysdate, 'HH:MI AM');
          sysdate           | to_char
----------------------------+----------
 2012-08-21 13:31:07.529333 | 13:31 PM
(1 row)

Notice the TO_CHAR shows 13:31 instead of 1:31 PM...

Update: Note that the HH format worked fine in Vertica 5 to convert to 12 hour clock when used in conjunction with AM or PM... I am having this issue in Vertica 6.

[JimKnicely]

Becky,

In Vertica 5 although the docs said HH has the range of 00-23, it actually did 01-12 when ever we added an AM or PM to the template in the TO_CHAR function. In Vertica 6 it must have been fixed so that we have to explicitly put HH12 when we want the 12 hour format. Although, I would concur that it doesn't make sense that HH:MI PM would produce military time. It's never 13:00 PM ...