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 )
AND (( cast(:enddate as timestamp(0)) - cast(d.DPEND_BRTH_DATE as timestamp(0)) ) year(4)) between 0 and 3
Can anyone please help?
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.
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?
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
transactiontime as of cast(:enddate as date) -- enddate is a string