topic Re: datetime format in sql pass through for Oracle in SAS Programming

datetime format in sql pass through for Oracle

checkno — Thu, 18 Aug 2011 16:17:39 GMT

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,

FriedEgg — Wed, 24 Aug 2011 16:33:02 GMT

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;

checkno — Thu, 25 Aug 2011 10:17:14 GMT

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!