Using Date format in WHERE clause in Oracle Table

[RDS2020]

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]

You need:

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

[ChrisNZ]

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.