Struggling with date variables

[EinarRoed]

In DI Studio, I have an extract transformation that needs to extract all rows where &extract_dttm is between valid_from_dttm and valid_to_dttm. So, I've set up this basic where-statement to start with:

&extract_dttm BETWEEN valid_from_dttm AND valid_to_dttm;

valid_from_dttm and valid_to_dttm are numeric variables with the format NLDATM21.

&extract_dttm is set up by means of this precode:

%let extract_dttm = %sysfunc(putn(%sysfunc(intnx(DTMONTH, %sysfunc(DATETIME()), -1, E)), NLDATM21.));

When using %put &extract_dttm; it appears like this in the log:

30.sep.12 23:59:59    (which looks just like valid_from_dttm and valid_to_dttm in the input table)

Apparently &extract_dttm can't be compared to the numeric valid_from_dttm and valid_to_dttm because it's a character. How can I edit the precode or where-statement to successfully extract the correct rows?

I'd greatly appreciate any insight, as I've been struggling with variations of this code for days now. I'm very inexperienced in using date and macro variables.

Thanks for your time.

[Reeza · Accepted solution]

All macro variables are characters.

To use them as date or date time values you can enclose them in quotations with the appropriate letters afterwards.

IE.

Date:

"01Apr2011"d

Date Time

"25dec2000:15:01"dt