Need help on DATE function

General

Need help on DATE function

Hi I have a select satatement in stored procedure,

Inthe where condition, i have

b.dt2_month_end =( DATE :THIS_END_DATE - EXTRACT(DAY FROM :THIS_END_DATE))--------statement A
i'm getting some syntax error.(I declared :THIS_END_DATE parameter before begin, so i think no prob with parameter)

if i give hard coded values like

b.dt2_month_end =( DATE '2011-03-31' - EXTRACT(DAY FROM '2011-03-31'))----it's giving data,
but statement A(above) not executing in procedure.

Finally what question is how to use date function to above statement.
It's a bit urgent
Thanks in Advance.
Tags (3)
4 REPLIES

Re: Need help on DATE function

may date - 31 is int type ;
Enthusiast

Re: Need help on DATE function

I don't think you need the DATE keyword. if :THIS_END_DATE is defined as a date data type, it should work just fine. The keyword is only required so Teradata will know to cast the character string to a date.
Junior Contributor

Re: Need help on DATE function

DATE '2011-03-31' is a date literal, which must be a single expression.

You have to change the condition depending on the datatype of THIS_END_DATE:
= (:THIS_END_DATE - EXTRACT(DAY FROM :THIS_END_DATE)) -- for a DATE
= (CAST (:THIS_END_DATE AS DATE) - EXTRACT(DAY FROM CAST (:THIS_END_DATE AS DATE))) -- for a CHAR

Dieter

Re: Need help on DATE function

Thanks for your help guys, I'll try and let you know.