Formatting Dates with the TO_CHAR Function

[JimKnicely]

If you’ve been using MySQL and SQL Server then you are probably familiar with the DATE_FORMAT (MySQL) and CONVERT (SQL Server) functions when you need to format a date string.

Vertica, like Oracle, has a function named TO_CHAR that can be used to format dates.

Examples:

Code: Select all

cognos=> SELECT current_timestamp                         "RIGHT NOW"
cognos->      , to_char(current_timestamp, 'MM/DD/YYYY')  "MM/DD/YYYY"
cognos->      , to_char(current_timestamp, 'Mon-YYYY')    "Mon-YYYY"
cognos->      , to_char(current_timestamp, 'YYYYMMDD')    "YYYYMMDD"
cognos->      , to_char(current_timestamp, 'HH:MI:SS AM') "HH:MI:SS AM/PM"
cognos->      , to_char(current_timestamp, 'HH24:MI:SS')  "HH24:MI:SS";
           RIGHT NOW           | MM/DD/YYYY | Mon-YYYY | YYYYMMDD | HH:MI:SS AM/PM | HH24:MI:SS
-------------------------------+------------+----------+----------+----------------+------------
2012-05-30 15:26:25.466157-04 | 05/30/2012 | May-2012 | 20120530 | 03:26:25 PM    | 15:26:25
(1 row)

Have fun!