This discussion is archived
9 Replies Latest reply: Sep 13, 2013 4:54 AM by EdStevens RSS

date format issue

siebelD Newbie
Currently Being Moderated


Hi experts

 

I am using oracle 11G database .

recently on our prod server ,i got stuck on date related issue .

 

yesterday when query the data base and checked the %code select created from s_src %code,it has given result on dd-Mon-YYYY format

again today I ran same query ,It showed result on DD-MON-YY format .

what could be differecne between today and yesterday ?if any hint ,it would be grt help .

 

Also for getting result always on same format ,i have planned to include below to_date function

%code select to_date('CREATED','DD-MON-YYYY') from s_src %code

 

Please let me know if it is optimum solution or we have any other .

 

Thanks

  • 1. Re: date format issue
    Raunaq Explorer
    Currently Being Moderated

    Use to_date to convert to your desired format.

  • 2. Re: date format issue
    bencol Pro
    Currently Being Moderated

    What is the datatype of s_src.created? If it is DATE, then you need to_char to display it as a string in the format you desire. If it is a varchar2, then I'd change it to a date. to_date(date) is a bug.

  • 3. Re: date format issue
    siebelD Newbie
    Currently Being Moderated


    created date data type is utc date time .Please let me know what function shoud use .

    to_char or to_date and also I am getting different date format on different days ,any hint ?

  • 4. Re: date format issue
    Paul Horth Expert
    Currently Being Moderated

    A date in a DATE datatype has an internal format that Oracle uses.

     

    When asked to display it, it must be converted to a human-readable string.

     

    If you don't say how you want to see it, the Oracle client you are using will use a default format. In some

    cases this would be the local session's NLS_DATE_FORMAT parameter.

     

    You may have looked at the date on two different machines with different settings.

     

    To avoid this, use TO_CHAR with a format mask to display the way you want.

  • 5. Re: date format issue
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    siebelD wrote:

     


    created date data type is utc date time .Please let me know what function shoud use .

    to_char or to_date and also I am getting different date format on different days ,any hint ?

    to format datetime datatype you can use to_char or setting nls_date_format parameter, but your client program may depend also. for example in WINDOWS regional settings , for example :

  • 6. Re: date format issue
    Purvesh K Guru
    Currently Being Moderated

    siebelD wrote:

     


    Hi experts

     

    I am using oracle 11G database .

    recently on our prod server ,i got stuck on date related issue .

     

    yesterday when query the data base and checked the %code select created from s_src %code,it has given result on dd-Mon-YYYY format

    again today I ran same query ,It showed result on DD-MON-YY format .

    what could be differecne between today and yesterday ?if any hint ,it would be grt help .

     

    Also for getting result always on same format ,i have planned to include below to_date function

    %code select to_date('CREATED','DD-MON-YYYY') from s_src %code

     

    Please let me know if it is optimum solution or we have any other .

     

    Thanks

     

    What is the datatype of column CREATED in table S_SRC?

     

    Since, you have used a TO_DATE, i assume it should be a VARCHAR2, which in itself is a receipe inviting trouble. Date values should always be stored in DATE datatype columns, which relaxes you from providing basic validations and storing corrupt data into your tables.

     

    If it is a Date datatype column, and you are able to see a difference between the format, it could be because of changed setting of NLS_DATE_FORMAT at your session or system level. This will cause the date to be formatted in the way you would like to see by default.

    If you face the same/similar issue again, try to check the NLS_DATE_FORMAT value from NLS_SESSION_PARAMETERS view.

     

    select *
      from nls_session_parameters
    where lower(parameter) like '%date_format%';

    PARAMETER                      VALUE                                   

    ------------------------------ ----------------------------------------

    NLS_DATE_FORMAT                DD-MON-RR


    select sysdate from dual;

     

    SYSDATE                  

    -------------------------

    13-SEP-13


    alter session set nls_date_format = 'DD-Mon-YYYY';

     

    session set altered.

     

    select *

      from nls_session_parameters

    where lower(parameter) like '%date_format%';

     

    PARAMETER                      VALUE                                   

    ------------------------------ ----------------------------------------

    NLS_DATE_FORMAT                DD-Mon-YYYY


    select sysdate from dual;

     

    SYSDATE                  

    -------------------------

    13-Sep-2013

  • 7. Re: date format issue
    siebelD Newbie
    Currently Being Moderated


    thank you !

     

    if my created date data type is utc date time ,

     

    is there needs to use to_char or to_date function for getting data on particular format .

    like to_char('created','DD-MON-YYYY') .

     

     

    I just wanted to be sure that if any time nls_date_format setting change for my server ,should not have impact on data format for that using to_char is fine?

  • 8. Re: date format issue
    Paul Horth Expert
    Currently Being Moderated

    As i said before, use TO_CHAR with a format mask.

  • 9. Re: date format issue
    EdStevens Guru
    Currently Being Moderated

    siebelD wrote:

     


    created date data type is utc date time .Please let me know what function shoud use .

    to_char or to_date and also I am getting different date format on different days ,any hint ?

     

    Try as I might, I cannot find any reference to a 'utc date time' data type.  The only date-time data types are DATE and TIMESTAMP.

     

    see: http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/ - But I want to store my date as ...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points