Invalid date

[traman]

The following "sql" was running fine till yesterday. This is is run once a month. But, when I ran it after the 2005 roll-over, it is giving me a problem. Does nayone has a clue?

THanks,
Ramana

(select date-3 mtd, (mtd+1) nt, cast(

(cast(extract(year from nt) as char(4) ) || '-' || ( cast(extract(month from nt) as char(2) ) ) || '-01') as date) lmt_dt

from dbc.dbcinfo )

This returns:

mtd nt lmt_dt

2004-12-30 2004-12-31 2004-12-01

2004-12-30 2004-12-31 2004-12-01



BUT…

(select date-2 mtd, (mtd+1) nt, cast(

(cast(extract(year from nt) as char(4) ) || '-' || ( cast(extract(month from nt) as char(2) ) ) || '-01') as date) lmt_dt

from dbc.dbcinfo )

will fail with invalid date.

 

[dnoeth]

Just remove the cast to date and you'll see the problem:
The resulting string is '2005- 1-01' and Teradata just doesn't like the blank within the month.

A simple solution is to add a proper format string:
( cast((extract(month from nt) (format '99')) as char(2) ) )

But as you just want to calculate the first day of a month:
"date - extract(day from date)" --> last day of the previous month

"date - extract(day from date) + 1" --> first day of the current month

Btw, no need for a from in a query, this is no Oracle ;-)

Dieter