topic Using Date format in WHERE clause in Oracle Table in SAS Programming

Using Date format in WHERE clause in Oracle Table

RDS2020 — Wed, 22 Jul 2020 01:16:16 GMT

Hi All,

When i am using below in SAS it is working fine:-

proc SQL ;
Select datepart(created_date) format=MONYY.
from ora.table1 ;
quit;

But when i am using same format in WHERE clause it is not working and giving error:-

PROC SQL ;
Delete from ORA.table1
WHERE datepart(created_date) format=MONYY. = put(today(),MONYY.) ;

What would be right way to get MONYY format in WHERE clause

Thanks,

RDS

ChrisNZ — Wed, 22 Jul 2020 01:41:12 GMT

You need:

WHERE put(datepart(CREATED_DATE),monyy.) = put(today(),monyy.)

ChrisNZ — Wed, 22 Jul 2020 01:45:03 GMT

It might better to use

WHERE month(CREATED_DATE)=month(today()) and year(CREATED_DATE)=year(today())

as this might readily be translated into a condition that Oracle can process.

Use option SASTRACE to check the Oracle query.