DATA Step, Macro, Functions and more

datetime format in sql pass through for Oracle

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

datetime format in sql pass through for Oracle

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,


Accepted Solutions
Solution
‎08-24-2011 12:33 PM
Trusted Advisor
Posts: 1,300

Re: datetime format in sql pass through for Oracle

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;

View solution in original post


All Replies
Solution
‎08-24-2011 12:33 PM
Trusted Advisor
Posts: 1,300

Re: datetime format in sql pass through for Oracle

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;

Occasional Contributor
Posts: 7

Re: datetime format in sql pass through for Oracle

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!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 1405 views
  • 0 likes
  • 2 in conversation