Changing datetime pattern in Vertica COPY

Moderator: NorbertKrupa

Post Reply
Beg1nner
Newbie
Newbie
Posts: 20
Joined: Fri Jul 24, 2015 8:46 pm

Changing datetime pattern in Vertica COPY

Post by Beg1nner » Mon Mar 21, 2016 7:34 pm

My source data has dates in the following manner

YYYYMMDD-HH:MM:SS

In my vertica database, I have created my column as datetime and the load is getting rejected. How should i convert the datetime on the manner accepted by vertica?

YYYY/MM/DD HH:MM:SS

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Changing datetime pattern in Vertica COPY

Post by JimKnicely » Mon Mar 21, 2016 8:48 pm

Probably the simplest way is to use the FORMAT option of the COPY command...

Example:

Code: Select all

dbadmin=> create table test (col1 datetime);
CREATE TABLE

dbadmin=> \! cat /home/dbadmin/test.txt
20160101-12:00:00

dbadmin=> copy test (col1 format 'YYYYMMDD HH:MI:SS') from '/home/dbadmin/test.txt' ;
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> select * from test;
        col1
---------------------
 2016-01-01 12:00:00
(1 row)
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Data Load”