date format

TSG_1

Hi,

I need to do bulk load in Vertica.  The data is a dump of an Oracle table.  I created a csv file by running Sql Loader and using this csv file as the input to load the Vertica table.  I chose to use COPY.  The data is getting loaded but I see the date format is MM/DD/YYYY which is not the same as the source table is formatted as 'DD-MON-YY'.  I checked the date format in the .csv file, it is 'DD-MON-YY' there too.  I went through many different posts on this topic and also the documentation.  It will be appreciated, if someone can help.  I am not posting my script here but if anyone wants to see my script, please let me know.  BTW, I am using Vertica version 5.1.  Thanks in advance.

Daniel_Leybovic

Hi!

Post few dates from file, it's enough. This is a format - '29-Jan-2013'?
http://vertica-forums.com/viewtopic.php?f=49&t=251&p=1004#p1004

daniel=> create table TSG (id int, nick varchar(16), dt date, income money);
CREATE TABLE

daniel=> \!cat /tmp/tsg.data
1|foo|29-Jan-2013|100.0
2|bar|30-Jan-2013|200.0
3|egg|31-Jan-2013|300.0
4|tux|01-Feb-2013|59.9

daniel=> copy TSG
daniel-> ( id, nick, dt format 'DD#MON#YYYY', income )
daniel-> from '/tmp/tsg.data'  direct  delimiter '|' abort on error;
 Rows Loaded 
-------------
           4
(1 row)

daniel=> select * from TSG;
 id | nick |     dt     |  income  
----+------+------------+----------
  1 | foo  | 2013-01-29 | 100.0000
  2 | bar  | 2013-01-30 | 200.0000
  3 | egg  | 2013-01-31 | 300.0000
  4 | tux  | 2013-02-01 |  59.9000
(4 rows)

Daniel

TSG_1

Thanks for your quick response.  It’s truly appreciated.

I am able to load the data.  The problem I am having is it’s not preserving the date format that’s in the input file. Your results show the same issue I am having.  In your test data, you have ’29-Jan-2013’ but the data in the table is stored as ‘2013-01-29’ as displayed by the select statement.  Is there a way to fix the format?

My other problem is the year part of the date field.   My input file has 31-DEC-99.  Although, it’s only showing 99, I know that it’s 31-DEC-9999.  When the data is loaded in my vertica table, it’s 1999.  I have formatted the column in the COPY statement

EFFECTIVE_END_DT FORMAT 'DD#MON#RR'

The result I am getting is 1999-12-31. 

I changed the format to

EFFECTIVE_END_DT FORMAT 'DD#MON#RRRR'

but I am still getting 1999-12-31

Any help will be much appreciated.

Thanks.

Daniel_Leybovic

>> Is there a way to fix the format?
Nothing here is wrong. Vertica can define a date format, but you are on wrong way - Vertica isn't a reporting tool, but a analytical. Pretty/beautiful output format (names of month instead of number, weekday name, whatever) must be defined in reporting tools - for that BI/Reporting tools are created and Vertica are created to analyze data.

https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/SET/SETDATE...

daniel=> select * from tsg;
 id | nick |     dt     |  income  
----+------+------------+----------
  1 | foo  | 2013-01-29 | 100.0000
  2 | bar  | 2013-01-30 | 200.0000
  3 | egg  | 2013-01-31 | 300.0000
  4 | tux  | 2013-02-01 |  59.9000
(4 rows)

daniel=> set datestyle to SQL, DMY;
SET
daniel=> select * from tsg;
 id | nick |     dt     |  income  
----+------+------------+----------
  1 | foo  | 29/01/2013 | 100.0000
  2 | bar  | 30/01/2013 | 200.0000
  3 | egg  | 31/01/2013 | 300.0000
  4 | tux  | 01/02/2013 |  59.9000
(4 rows)

>> My other problem is the year part of the date field.   My input file has 31-DEC-99.
I can't help you here - continue to read Oracle docs for solving your problem in Vertica. Sorry, I can't do it.
Where did you find a flag "RR"? Did you tried an banal flag "YY" - 2 digits for year?

Template Patterns for Date/Time Formatting
https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Formatting/T...

daniel=> select to_date('31-DEC-99', 'DD-MON-YY'), to_date('1-JAN-00', 'DD-MON-YY');
  to_date   |  to_date   
------------+------------
 31/12/1999 | 01/01/2000
(1 row)