data timestamp issues

Database
Fan

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

Tags (2)
3 REPLIES
Junior Contributor

Re: data timestamp issues

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.

Fan

Re: data timestamp issues

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

Junior Contributor

Re: data timestamp issues

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