Ora 9i client nls_date_format setting

[kris123]

Hi Friends,

I have not modified any settings on the client side parameters yet.
Where do i go or what file should I edit to set the nls_date_format
at the client? Is there a counter part init.ora?

I believe the default is still "DD-MON-YY", but one
of the apps dev claimed that it is "YYYY-MM-DD".
He is connecting to our 8iDB via OLE DB and
you know he got problem in searching rows by date.
He can't get a match if he use the condition like:

Select ename from emp where empdate='01-JAN-05';

But if i run it from sqlplus i do hit a match.

The developer can hit a match if he uses to_char date coversion.

Do u thinks its all about nls_date_foramt settings?

Thanks in advance

[anandkl]

Hi,
Ask the developer if he has set any date format in you application and searching for a wrong format.

Default is still dd-mon-yyyy

regards
anandkl

[kris123]

hun, he has not set any date format on the apps side, but he said
he got s match if he uses the "yyyymmdd" format. Can u tell me if
there are parameter set for nls_date_format at the client side?

Thanks again

[oracbase]

Hi,

Just set nls_date_format on your client enviroment setting.

e.g in Win2k >set NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS', or put this in the windows environment settings

Cheers
Oracbase

[kris123]

thanks dear

[DaPi]

Originally posted by kris123
Select ename from emp where empdate='01-JAN-05';

If you want your code to be "bomb-proof" then you will have to always code it like:
Select ename from emp where empdate=to_date('01-JAN-2005','DD-MON-YYYY');

BTW: Select sysdate from dual; will quickly show you your current default.

[kris123]

Thanks dear, I like that bomb-proof code u got. I saw it before, but
always forget how to code it. The important use of it is
when u have index on the empdate. The index wont be activated if u
use functions on the date column (like to_char,to_date,substr,etc),
right? What if i include hour,min,sec in the comparison? Will the
code be still the same? I mean will i get the matching hr,min,sec too?

Select ename from emp where
empdate=to_date('01-JAN-2005 01:30:50','DD-MON-YYYY hh:mi:ss');

Thanks

[abhaysk]

If the functions are used on the column then index wont be used, but here its not the case.. to_date fn is used on a string.. so your indexes would be used if CBO thinks its good..

To your other qry

What if i include hour,min,sec in the comparison? Will the
code be still the same? I mean will i get the matching hr,min,sec too?

Yes..


I would suggest you better brush up ur concepts ... (being a senior member in this group and ~5 yrs of exp, atleast i would not expect you ask such Qs )

Abhay.

[kris123]

Yeah dear, i guess ur right But if u wont use what u learned often,
or ur job doesnt require it a lot, chances are u forget it.

[kris123]

Ooohh dear, good thing u remind me. Can i update/change my registry
date to make it go back to a later date?