Convert a String to a Date, Including Unconventional Dates

Posted March 26, 2019 by James Knicely, Vertica Field Chief Technologist

The built-in Vertica function TO_DATE converts a string value to a DATE type.


We all know that there are 31 days in March. dbadmin=> SELECT TO_DATE('03/31/2019', 'MM/DD/YYYY'); TO_DATE ------------ 2019-03-31 (1 row) But are there 32 or 33 days in March? dbadmin=> SELECT TO_DATE('03/32/2019', 'MM/DD/YYYY'); TO_DATE ------------ 2019-04-01 (1 row) dbadmin=> SELECT TO_DATE('03/33/2019', 'MM/DD/YYYY'); TO_DATE ------------ 2019-04-02 (1 row) No. Vertica simply extends the date returned into the next month!

Also note there is no 13th month! dbadmin=> SELECT TO_DATE('13/31/2019', 'MM/DD/YYYY'); TO_DATE ------------ 2020-01-31 (1 row) Helpful Link:

Have fun!