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
Changing datetime pattern in Vertica COPY
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Changing datetime pattern in Vertica COPY
Probably the simplest way is to use the FORMAT option of the COPY command...
Example:
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.