Format Oracle number (YYYYMMDD) to SAS date9 using a pass-through query

Question

How can I convert a date stored as a number (length 8, format 20.) from Oracle (YYYYMMDD) to a SAS date9 format within a pass-through query?

I tried to_char(f_date) and to_date(to_char(f_date),'YYYYMMDD')

But I don't know how to apply the SAS format (date9.) within the passthough query now

Answer 1

As you commented:

• if you pass 20221130
• you want to get 30NOV2022

then

SQL> select to_char (to_date ('20221130', 'yyyymmdd'), 'ddMONyyyy', 'nls_date_language=english') result
  2  from dual;

RESULT
---------
30NOV2022

SQL>

Answer 2

If f_date is a date then you can just convert it to a string in the format you want:

to_char(f_date, 'DDMONYYYY', 'nls_date_language=english')

If it is a number - which it shouldn't be - then you'll need to convert that to a date first:

to_char(to_date(to_char(f_date), 'YYYYMMDD'), 'DDMONYYYY', 'nls_date_language=english')

In both cases I've included the optional third argument to to_char() to force the month abbreviation to English regardless of the users session settings.

fiddle

Answer 3

On the Oracle side you can convert it to a DATE value using TO_DATE() function. You might need to first convert number to a string using the TO_CHAR() function.

From the SAS side you should then see a DATE value. If you instead see it as DATETIME value then you could try using the DBSASTYPE= dataset option to tell SAS that you would like the variable created as DATE type values (number of days) instead of DATETIME type values (number of seconds). Or use the DATEPART() function to convert it to date value on the SAS side

If you pull it across as a number as you describe (that is today's date of 30NOV2022 would be the number 20,221,130) then you can convert it to date values on the SAS side using INPUT() and PUT() functions.

date = input(put(date,z8.),yymmdd8.);

Once you have it as date values on the SAS side you can attach any of the many SAS formats that know how to display date values in ways that humans can understand. Such as the DATE9. format.