DATE FORMAT while creating new table

[mayankgupta18]

Hello

Can we specify DATE FORMAT while creating new table ?

CREATE TABLE TEST_D( A DATE FORMAT 'yyyy-mm-dd');

will the above command work in Vertica ?

If not then where I can change default DATE format ?

[JimKnicely]

Hello,

Welcome to to the community!

The command "CREATE TABLE TEST_D( A DATE FORMAT 'yyyy-mm-dd');" will not work in Vertica.

What is the goal of having that format? Is it to get rid of time? If so, you are in luck. The DATE data type doesn't include time.

Code: Select all

dbadmin=> CREATE TABLE TEST_D( A DATE );
CREATE TABLE
dbadmin=> INSERT INTO TEST_D VALUES ( NOW() );
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT NOW(), A FROM TEST_D;
              NOW              |     A
-------------------------------+------------
 2013-02-07 06:32:02.224478-05 | 2013-02-07
(1 row)

Notice how the value in the A column only contains the date... there is no time.

I hope this helps!

[mayankgupta18]

I want to use FORMAT while creating tables just to enable vertica to load data using COPY without specifying FORMAT there in COPY command.

For example:
If we have created table with DATE format 'DD-MM-YYYY' then I can load following set of records:

21-5-2012
31-3-2012

Because if we have data from Teradata then we will be having in above format.

Hope this will clarify my need to set Default DATE format 'DD-MM-YYYY'

[JimKnicely]

Oh

In that case, yes, you will have to change the default date style...

Code: Select all

dbadmin=> create table t (c date);
CREATE TABLE

The following INSERT doesn't work since the default date style is YMD:

Code: Select all

dbadmin=> insert into t values ('21-5-2012');
ERROR 2992:  Date/time field value out of range: "21-5-2012"
HINT:  Perhaps you need a different "datestyle" setting

Need to change the date style:

Code: Select all

dbadmin=> set datestyle to 'DMY';
SET
dbadmin=> insert into t values ('21-5-2012');
 OUTPUT
--------
      1
(1 row)

Now these INSERTs work:

Code: Select all

dbadmin=> insert into t values ('31-3-2012');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from t;
     c
------------
 2012-05-21
 2012-03-31
(2 rows)