Ora 9i client nls_date_format setting
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Ora 9i client nls_date_format setting

  1. #1
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639

    Ora 9i client nls_date_format setting

    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

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  3. #3
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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

  4. #4
    Join Date
    Apr 2001
    Posts
    110
    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
    What's next after 10g?

  5. #5
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    thanks dear

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: Ora 9i client nls_date_format setting

    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.

  7. #7
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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.

  10. #10
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Ooohh dear, good thing u remind me. Can i update/change my registry
    date to make it go back to a later date?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width