1 2 Previous Next 15 Replies Latest reply: Apr 23, 2013 1:59 AM by user575115 RSS

    DATE format

    user575115
      Hi,
      In one of the query used like below is it correct or any need to be correct for date format?
      currently it's returning two tyoe of sets 1.NUll 2.01-JAN-00

      SELECT withdrawn_date
        FROM 
       csd  where  NVL(csd.withdrawn_date,'01-JAN-1900') = '01-JAN-1900';
        • 1. Re: DATE format
          nkvkashyap
          Yes it will return two records, it is matching NULL value as you are using NVL command and the other record (not null) it is matching with where condition.
          create table csd(withdrawn_date date);
          
          insert into csd values(null);
          
          insert into csd values('01-JAN-1900');
          
          SELECT withdrawn_date
            FROM 
           csd  where  NVL(csd.withdrawn_date,'01-JAN-1900') = '01-JAN-1900';
          • 2. Re: DATE format
            Manik
            Try this: -- Untested from my side.
            SELECT withdrawn_date
              FROM csd
             WHERE NULLIF (csd.withdrawn_date, '01-JAN-1900') IS NULL;
            Cheers,
            Manik.
            • 3. Re: DATE format
              user575115
              Here my doubt is it's returning data for 2000 or 1900?
              • 4. Re: DATE format
                nkvkashyap
                refer this:
                http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/difference-between-yyyy-and-rrrr-format-519496
                • 5. Re: DATE format
                  Manik
                  http://www.orafaq.com/forum/t/124512/2/

                  Cheers,
                  Manik.
                  • 6. Re: DATE format
                    user575115
                    I was tried like below and same result for both.

                    NVL(to_date(csd.withdrawn_date,'DD-MON-RR'),'01-JAN-00') = '01-JAN-00';
                    NVL(to_date(csd.withdrawn_date,'DD-MON-YY'),'01-JAN-00') = '01-JAN-00';
                    • 7. Re: DATE format
                      nkvkashyap
                      If the specified two digits year is 0-49 and the two digits of the current
                      year is 0-49 it returns the current century.
                      select to_date('01-JAN-2000','DD-MON-YYYY'),to_date('01-JAN-2000','DD-MON-RRRR'),
                      to_date('01-JAN-1900','DD-MON-YYYY'),to_date('01-JAN-1900','DD-MON-RRRR')
                      from dual;
                      If the specified two digits year is 0-49 and the two digits of the current
                      year is 50-99 it returns the century after the current one.
                      If the specified two digits year is 50-99 and the two digits of the current
                      year is 0-49 it returns the century before the current one.
                      If the specified two digits year is 50-99 and the two digits of the current
                      year is 50-99 it returns the current century.
                      • 8. Re: DATE format
                        user575115
                        Yes correct.
                        But we can't check like below right?.Since we will use only two digits instead of 4 digits while using with column.
                        NVL(to_date(csd.withdrawn_date,'DD-MON-YYYY'),'01-JAN-2000') = '01-JAN-2000';

                        we can check like below.
                        NVL(to_date(csd.withdrawn_date,'DD-MON-RRRR'),'01-JAN-2000') = '01-JAN-2000';
                        • 9. Re: DATE format
                          Marwim
                          There is no difference between 'DD-MON-YYYY' and 'DD-MON-RRRR'. It will always use four digit years, so the examples from nkvkashyap make no sense. The output will only differ when you use 'DD-MON-YY' and 'DD-MON-RR' on data that only hold 2 digit year values. But using two digit years (aside from displaying in a GUI) is plain stupid. It is only done by people who learned nothing from Y2K.

                          Regards
                          Marcus

                          Edited by: Marwim on 19.04.2013 12:48
                          • 10. Re: DATE format
                            Marwim
                            In one of the query used like below is it correct or any need to be correct for date format?
                            Whether the query is correct or not depends on the requirement. Do you want to return rows where withdrawn_date is either NULL or 01-JAN-1900? Then it is ok. Do you want to return values from a row where withdrawn_date is NULL only when there is no row with 01-JAN-1900, then it is wrong.

                            The requirement determins whether the query is correct or not. As long as you don't tell us what you expect we will not be able to help you.
                            Post some sample data and the expected output as described in {message:id=9360002}

                            Regards
                            Marcus
                            • 11. Re: DATE format
                              Marwim
                              SELECT withdrawn_date
                                FROM 
                               csd  where  NVL(csd.withdrawn_date,'01-JAN-1900') = '01-JAN-1900';
                              Is withdrawn_date a DATE column? Then the query should not depend on the implicit conversion of '01-JAN-1900' to a date. Instead use
                              NVL(csd.withdrawn_date,DATE '1900-01-01') = DATE '1900-01-01'
                              or
                              NVL(csd.withdrawn_date,TO_DATE ('01-01-1900','DD-MM-YYYY')) = TO_DATE ('01-01-1900','DD-MM-YYYY')
                              If withdrawn_date is not a DATE column, then the data model is wrong, sooner or later leading to data corruption.

                              Since you say that you get 01-JAN-00 as output I assume, that it is a DATE column and the display format of the output for DATE values in your GUI is set to DD-MON-YY. It seems that the database default format is different, otherwise your query would not work.

                              Regards
                              Marcus
                              • 12. Re: DATE format
                                user575115
                                Thanks.withdrawn_date is date column only.
                                And i'm not checking in any front end ,i'm checking in database only(sql developer).Default date format is DD-MON-YY
                                only.correct? hence i'm getting output 01-JAN-00 and NULL
                                • 13. Re: DATE format
                                  Marwim
                                  And i'm not checking in any front end ,i'm checking in database only(sql developer)
                                  In this case SQL Developer is your frontend
                                  Default date format is DD-MON-YY only.correct?
                                  There is a default setting for the display of DATA columns in SQL Developer (Preferences - Database - NLS). You can change it there to see four digit years.

                                  You can find the current date format used in your session with
                                  SELECT * FROM nls_session_parameters
                                  WHERE parameter = 'NLS_DATE_FORMAT'
                                  Keep in mind that implicit conversion depends on the settings for the current session and can lead to a lot of unexpected errors. Therefore you should always use an explicit conversion with the expected format mask.

                                  Regards
                                  Marcus
                                  • 14. Re: DATE format
                                    William Robertson
                                    Writing dates as strings is a dangerous habit to get into, even though you see it so often people might consider it standard.

                                    The simplest and most universally reliable way to specify a date literal is like this:
                                    date '1900-01-01'
                                    I would advise anyone to get into the habit of using it every time.
                                    1 2 Previous Next