All Forums General
anilk.zee 4 posts Joined 12/10
12 Apr 2011
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.

lianfeng 1 post Joined 04/11
13 Apr 2011

may date - 31 is int type ;

mnylin 118 posts Joined 12/09
13 Apr 2011

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.

dnoeth 4628 posts Joined 11/04
13 Apr 2011

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

Dieter

anilk.zee 4 posts Joined 12/10
15 Apr 2011

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

You must sign in to leave a comment.