i'm querying an oracle database with sql pass through
the issue is with the format of the datetime passed.
On Oracle this query return the error 01843 not a valid month.
select to_date('18aug11:17:36:34','ddmonyy:hh24:mi:ss') from dual
this is fixed by changing AUG to AOU, cause my nls_date_language=french under Oracle
The problem is when i execute the equivalent pass trough for the fixed query from my SEG, i have the opposite behavior:
the query with AUG is OK
and the query with AOU return an ERROR: ORACLE execute error: ORA-01843: not a valid month.
just for information, under sas also my locale language is set to FRENCH_FRANCE.
Cheers,
I would double check your language setting through the sas pass-through session:
SELECT *
FROM NLS_DATABASE_PARAMETERS
WHERE parameter IN ( 'NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');
You can change these on a per session basis by:
execute by oracle(ALTER SESSION SET NLS_LANGUAGE = FRENCH_FRANCE.WE8ISO8859P1);
You might also look into altering the NLS parameters on your executing server for example in unix:
%sysexec(export NLS_LANG=FRENCH_FRANCE.WE8ISO8859P1);
You can lookup your valid languages using:
SELECT parameter, value
FROM V$NLS_VALID_VALUES;
More simply:
You can pass the optional NLS_DATE_LANGUAGE parameter to your TO_DATE function
SELECT TO_DATE('1-Août-01','DD-MON-YY','NLS_DATE_LANGUAGE=FRENCH') FROM DUAL;
I would double check your language setting through the sas pass-through session:
SELECT *
FROM NLS_DATABASE_PARAMETERS
WHERE parameter IN ( 'NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');
You can change these on a per session basis by:
execute by oracle(ALTER SESSION SET NLS_LANGUAGE = FRENCH_FRANCE.WE8ISO8859P1);
You might also look into altering the NLS parameters on your executing server for example in unix:
%sysexec(export NLS_LANG=FRENCH_FRANCE.WE8ISO8859P1);
You can lookup your valid languages using:
SELECT parameter, value
FROM V$NLS_VALID_VALUES;
More simply:
You can pass the optional NLS_DATE_LANGUAGE parameter to your TO_DATE function
SELECT TO_DATE('1-Août-01','DD-MON-YY','NLS_DATE_LANGUAGE=FRENCH') FROM DUAL;
after checking, my NLS_LANGUAGE under Oracle is : American,
And both this proposed solutions are working, i'll stay on the NLS_DATE_LANGUAGE parameter of TO_DATE for convenience.
Thanks,
The issue seems to be with TOAD, he accept 01Aou and not 01aug whith American as language!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.