getting date month from oracle table with proc sql in sas

[rajeshm]

connected to database with lib statement. and using proc sql to fetch table contents from the db.

q1: how to fetch the month number(ex:08) from date?

q2: how to fetch date in 11AUG2020 format. currently getting date with timestamps.

[Tom]

I think that Oracle does not have a DATE type. Only DATETIME.

Do you need to get the month after the data has come to SAS or are you asking for Oracle syntax to use in passthru code?

In SAS you can use the DATEPART() function to convert your datetime values into date values (basically divide by the number of seconds in a day).  You can then use the MONTH() function to get the month number from the date value.  If you want the dates to display in that style attach the DATE9 format to the date variable.

libname myora oracle .... ;
proc sql;
create table want as
  select id
       , datepart(oracle_date) as sas_date format=date9.
       , month(calculated sas_date) as sas_month format=z2.
  from myora.mytable
;
quit;

[rajeshm]

Thanks a lot. Oracle has Date datatype.

helpful: Yes, Your solution is working as expected.

combined 2steps:

month(datepart(post_date)) as month,  is also working fine.