date format problum

Analytics
Enthusiast

date format problum

in my table

PRIME_FLIGHT_END_RANGE CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ',
MIGRATION_START_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE ,
MIGRATION_END_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE ,
DATE_TIMEZONE_REF CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT ' ',
LAST_UPDATE_TSMP TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0))

INSERT INTO RAHUL_DB.DATE_TABLE

VALUES('0001',20071027,20991231,);

but when i do select option teh out put of date is as

10/27/3907 12/31/3999

1900 years is getting added to the date which i have entered

can anyone tell me the reasion and how to debug it

thank you in advance
RAHUL :)

2 REPLIES
Enthusiast

Re: date format problum

You need to use a date constant. Try this instead:

INSERT INTO RAHUL_DB.DATE_TABLE
VALUES('0001',date '2007-10-27',date '2099-12-31',,);

Enthusiast

Re: date format problum

Rahul,

you are probably inserting the date in incorrect format.as per my understanding you want to store date '2007/10/27' into your table but you ended up inserting 3907/10/27.

your insert should have been as follows
VALUES('0001','2007/10/27','2099/12/31',....);

this would store the correct date values into your table.

Internally teradata stores the date values in integer format.Here you have attempoted to directly insert an integer into the date colunmn. Later on,When you queried the table for date column, the integer date has been converted into appropriate representation.per my understanding, when you inserted 20071027 into the table, teradata understands it as date 3907/10/27.