All Forums Database
dieter 3 posts Joined 09/14
15 Jan 2015
data timestamp issues

Hi,
 
I am trying to write a macro because I need to run this monthly. I have to run this monthly for different groups of potential, which I want to pass by parameters.
In the past, I used the current_date, and it worked, but now I would like to pass the data as a parameter and use that one, but that doesn't work:
-- this worked

AND ( (current_date - pr.BRTH_DATE) year(4) between 25 and 55 OR pr.BRTH_DATE is null )

 

 
-- this doesn't
( this is my parameter "enddate varchar(10)", which I pass the following value '2014-12-31' -- i use this parameter in other parts of my macro, and that works, such as "

AND EMAIL_DPLOYMT_DATE BETWEEN :startdate(date,format'YYYY-MM-DD') AND :enddate(date,format'YYYY-MM-DD')

" )

AND (( ( DATE :enddate - pr.BRTH_DATE ) year(4)) between 25 and 55 OR pr.BRTH_DATE is null )

 

I have also tried

 

AND (( cast(:enddate as timestamp(0))  - cast(d.DPEND_BRTH_DATE as timestamp(0)) ) year(4)) between 0 and 3

 

 

Can anyone please help?
 
Thanks,
 
Dieter

dnoeth 4628 posts Joined 11/04
15 Jan 2015

Hi Dieter,
in your working example you explicitly cast the parameter to a date, of course this works.
But "DATE :enddate" is no valid syntax, only literal dates can be used (e.g. DATE '2015-01-15) but no colum reference.
So either cast it to a date or better define the input param as a DATE.

Dieter

dieter 3 posts Joined 09/14
16 Jan 2015

Hi Dieter,
Thanks a lot. No idea why I didn't try making the parameter a DATE.
I am also trying to use the following, using the same param, but it's not working.

transactiontime as of date :enddate

Any ideas here, please?
Thanks,
Dieter

dnoeth 4628 posts Joined 11/04
16 Jan 2015

Hi Dieter,
it's the same problem, DATE '2015-01-16' is a date literal, like TIME '12:34:56'.
So it's either 

transactiontime as of :enddate -- enddate is a DATE

or

transactiontime as of cast(:enddate as date) -- enddate is a string

 

Dieter

You must sign in to leave a comment.