9 Replies Latest reply: Sep 13, 2013 6:54 AM by EdStevens RSS

    date format issue

    siebelD


      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

          Use to_date to convert to your desired format.

          • 2. Re: date format issue
            bencol

            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


              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

                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

                  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

                    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


                      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

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

                        • 9. Re: date format issue
                          EdStevens

                          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 ...