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 ?
DATE FORMAT while creating new table
Moderator: NorbertKrupa
-
- Newbie
- Posts: 3
- Joined: Thu Feb 07, 2013 7:58 am
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: DATE FORMAT while creating new table
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.
Notice how the value in the A column only contains the date... there is no time.
I hope this helps!
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)
I hope this helps!
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.
-
- Newbie
- Posts: 3
- Joined: Thu Feb 07, 2013 7:58 am
Re: DATE FORMAT while creating new table
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'
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
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: DATE FORMAT while creating new table
Oh
In that case, yes, you will have to change the default date style...
The following INSERT doesn't work since the default date style is YMD:
Need to change the date style:
Now these INSERTs work:
In that case, yes, you will have to change the default date style...
Code: Select all
dbadmin=> create table t (c date);
CREATE TABLE
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
Code: Select all
dbadmin=> set datestyle to 'DMY';
SET
dbadmin=> insert into t values ('21-5-2012');
OUTPUT
--------
1
(1 row)
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)
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.