SAS Oracle where clause on date variable

[ArseneWenger]

Hi,

I am trying to subset oracle dataset on date variable which is numeric and has sas format datetime27.6. for eg. it looks like: 01JAN1960:13:01:01.000000

attached is what i have my code currently..

I am getting error with following query. Please advice correct way of using datetime subset.

Thanks in advance.

[Reeza]

It has to be valid SQL syntax for your server, which it isn't.

Replace the macro variable with the value to see what you need to do, possibly include quotes?

[Reeza]

EDIT: I'd post the modification to show but I don't want to type out your code. Post as text in the future please.

[Patrick]

Your macro variable &my_date must resolve to something like: '11JAN2017:12:34:57'

Looking at picture you've posted, I believe you're missing the single quotes around the date string and the column between yyyy and HH24.

Oh... and reading your initial post again, I also come to realize that you're applying a SAS DateTime format on a SAS Date value. That's why you end up with a year of 1960.

I am trying to subset oracle dataset on date variable which is numeric and has sas format datetime27.6. for eg. it looks like: 01JAN1960:13:01:01.000000

On the SAS side: Use format DATE9.  This will give you a string like 11JAN2017

On the Oracle side use something like:

where trunc(e.timestamp,'DATE') > to_date(&my_date,'ddmonyyyy')  

....don't forget to wrap the single quotes around the date string value!

https://docs.oracle.com/cd/E29805_01/server.230/es_eql/src/cdfp_analytics_lang_trunc.html

And last but not least:

When using SQL pass-through then I'd always first use a native database client to develop the code (i.e. SQL Developer). Only when the code works copy paste it into a SAS pass-through block and add your customizations like SAS macro variables.