Inserting Date as 0000-00-00

[maverick]

Hi All,

We are trying to load a table with a column having Datatype as 'DATE' and inserting the value 00000000, worked fine but when we query the table we are getting a suffix 'BC' as show below. Is there anyway we can avoid it?

=> copy PUBLIC.TEST (column1 format 'YYYYMMDD') from stdin ABORT ON ERROR delimiter '|' TRAILING NULLCOLS DIRECT NO ESCAPE;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 00000000
>> 00000000
>> \.

=> SELECT * from PUBLIC.TEST ;
column1
---------------
0001-01-01 BC
0001-01-01 BC
(2 rows)

Any help would be really appreciated.

[JimKnicely]

Hi Maverick,

What are you expecting the result to be? 0001-01-01 AD?

[maverick]

Hi Jim,

Thanks for your response, yeah I was expecting it to be 0001-01-01.

[JimKnicely]

Looks like Vertica to doing a TO_DATE on the '00000000' values...

Code: Select all

dbadmin=> select to_date('00000000', 'YYYYMMDD');
    to_date
---------------
 0001-01-01 BC
(1 row)

There is no year 0 in the Gregorian calendar. I guess internally the TO_DATE function decides to use BC when it evaluates the 0000 in the YYYY format.

If you want AD, you may have to do some transformation yourself. Something like the following maybe?

Code: Select all

dbadmin=> copy test (c1_r filler varchar(8), c1 as decode(to_date(c1_r, 'yyyymmdd'), to_date('00000000', 'yyyymmdd'), to_date('00010000', 'yyyymmdd'), to_date(c1_r, 'yyyymmdd'))) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 00000000
>> 20130626
>> \.
dbadmin=> select * from test;
     c1
------------
 0001-01-01
 2013-06-26
(2 rows)