date format

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)

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=> 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)

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)