9 Replies Latest reply: Nov 21, 2012 1:08 PM by nick woodward RSS

    trunc date function not working correctly

    nick woodward
      Hi,

      Another quick question if people don't mind. Bit confused about the trunc date function. I'm following the sql fundamentals exam guide and using their examples:

      select trunc(to_date('02-jun-2009 13:00','dd-mon-yyyy hh24:mi')) day from dual;

      this returns the 2nd of june, as it should, because, as far as i'm aware, leaving the optional variable out defaults the precision to 'day'. but when i explicitly add the variable 'day', as the guide has done with 'week' 'month' and 'year' in the following examples (so i assume this format is correct rather than dd/mon/yyyy), something goes wrong:

      select trunc(to_date('02-jun-2009 13:00','dd-mon-yyyy' hh24:mi'), 'day') day from dual;

      this returns the 31st of may. which is incorrect.

      however replacing 'day' with 'dd' provides the correct answer of the 2nd again.

      this isn't a major issue, it just bothers me that the guide seems (again) to be mistaken, something that is rapidly becoming a trend in their examples. i'd also quite like to know why this is happening, as it will help improve my understanding of sql in general - perhaps there is some sort of default to allow the correct use of the variable 'day' i'm overlooking and that the guide hasn't made clear.

      btw, i'm working in sqlplus - although developer has some odd results too.

      thanks alot,

      nick
        • 1. Re: trunc date function not working correctly
          BluShadow
          967660 wrote:
          Hi,

          Another quick question if people don't mind. Bit confused about the trunc date function. I'm following the sql fundamentals exam guide and using their examples:

          select trunc(to_date('02-jun-2009 13:00','dd-mon-yyyy hh24:mi')) day from dual;

          this returns the 2nd of june, as it should, because, as far as i'm aware, leaving the optional variable out defaults the precision to 'day'. but when i explicitly add the variable 'day', as the guide has done with 'week' 'month' and 'year' in the following examples (so i assume this format is correct rather than dd/mon/yyyy), something goes wrong:

          select trunc(to_date('02-jun-2009 13:00','dd-mon-yyyy' hh24:mi'), 'day') day from dual;

          this returns the 31st of may. which is incorrect.
          Please show us how you are doing this with a copy/paste from SQL*Plus.... as it works ok for me... (and you had a syntax error in what you posted)
          SQL> select trunc(to_date('02-jun-2009 13:00','dd-mon-yyyy hh24:mi'), 'day') day from dual;
          
          DAY
          --------------------
          01-JUN-2009 00:00:00
          • 2. Re: trunc date function not working correctly
            Frank Kulash
            Hi, Nick,
            967660 wrote:
            ... however replacing 'day' with 'dd' provides the correct answer of the 2nd again.
            Right. Think of 'DD' as being the default 2nd argument, not 'DAY'.
            TRUNC (dt, 'DAY')
            actually returns the beginning of the week that contains dt, where NLS_TERRITORY determines when the week begins.

            If you find this confusing, you've got lots of company.
            • 3. Re: trunc date function not working correctly
              Solomon Yakobson
              967660 wrote:
              this returns the 31st of may. which is incorrect.
              It is correct. Did you read date ROUND/TRUC docs? Format DAY rounds/truncates to starting day of the week. June 2, 2009 was Tuesday so TRUNC returns May 31 which was Sunday - first day of the week in USA.
              however replacing 'day' with 'dd' provides the correct answer of the 2nd again.
              DD rounds/trucates to beginning of day.

              SY.
              • 4. Re: trunc date function not working correctly
                Solomon Yakobson
                You got June 1, since you are is a country where week starts Monday.

                SY.
                • 5. Re: trunc date function not working correctly
                  6363
                  967660 wrote:

                  select trunc(to_date('02-jun-2009 13:00','dd-mon-yyyy hh24:mi')) day from dual;

                  this returns the 2nd of june, as it should, because, as far as i'm aware, leaving the optional variable out defaults the precision to 'day'.
                  It defaults to DD which is start of day.

                  >
                  select trunc(to_date('02-jun-2009 13:00','dd-mon-yyyy' hh24:mi'), 'day') day from dual;

                  this returns the 31st of may. which is incorrect.
                  DAY returns Starting day of the week in this case Sunday which is 05/31/2009 so it is correct.

                  This can all be looked up in the manuals

                  http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions255.htm#i1002084

                  http://www.oracle.com/pls/db112/homepage
                  however replacing 'day' with 'dd' provides the correct answer of the 2nd again.
                  See above
                  this isn't a major issue, it just bothers me that the guide seems (again) to be mistaken, something that is rapidly becoming a trend in their examples. i'd also quite like to know why this is happening, as it will help improve my understanding of sql in general - perhaps there is some sort of default to allow the correct use of the variable 'day' i'm overlooking and that the guide hasn't made clear.
                  I would recommend reading manuals I can't speak for the guide you refer to.
                  • 6. Re: trunc date function not working correctly
                    Paul  Horth
                    967660 wrote:
                    Hi,

                    Another quick question if people don't mind. Bit confused about the trunc date function. I'm following the sql fundamentals exam guide and using their examples:

                    select trunc(to_date('02-jun-2009 13:00','dd-mon-yyyy hh24:mi')) day from dual;

                    this returns the 2nd of june, as it should, because, as far as i'm aware, leaving the optional variable out defaults the precision to 'day'. but when i explicitly add the variable 'day', as the guide has done with 'week' 'month' and 'year' in the following examples (so i assume this format is correct rather than dd/mon/yyyy), something goes wrong:

                    select trunc(to_date('02-jun-2009 13:00','dd-mon-yyyy' hh24:mi'), 'day') day from dual;

                    this returns the 31st of may. which is incorrect.

                    however replacing 'day' with 'dd' provides the correct answer of the 2nd again.

                    this isn't a major issue, it just bothers me that the guide seems (again) to be mistaken, something that is rapidly becoming a trend in their examples. i'd also quite like to know why this is happening, as it will help improve my understanding of sql in general - perhaps there is some sort of default to allow the correct use of the variable 'day' i'm overlooking and that the guide hasn't made clear.

                    btw, i'm working in sqlplus - although developer has some odd results too.

                    thanks alot,

                    nick
                    'day' doesn't trunc to the beginning of the day. It truncates to the first day of the week: in your case 31st May.

                    'ddd' truncates to the beginning of the day.

                    So, leaving the second parameter out defaults to 'ddd' not 'day'.
                    • 7. Re: trunc date function not working correctly
                      nick woodward
                      sorry for wasting your time guys, it was the manual again, but i should've double checked the documentation. apologies!

                      Here is the quote from the book, which is, disturbingly, an official exam guide (sql fundamentals by jon watson, oracle press)

                      *"The date precision format parameter specifies the degree of truncation and is optional. If it is*
                      *absent, the default degree of truncation is +day+."*

                      It then goes on to talk of truncation to 'w' weeks, 'month' months, and 'year' years. No mention of 'dd', and every indication that 'day' actually defaults to that day, not the start of the week. This is one of many mistakes/poorly explained concepts that i've spent too long trying to understand.


                      thanks again for all the replies though.

                      nick

                      Edited by: 967660 on 21-Nov-2012 08:32
                      • 8. Re: trunc date function not working correctly
                        Frank Kulash
                        Hi, Nick,
                        967660 wrote:
                        ... Here is the quote from the book, which is, disturbingly, an official exam guide (sql fundamentals by jon watson, oracle press)

                        *"The date precision format parameter specifies the degree of truncation and is optional. If it is*
                        *absent, the default degree of truncation is +day+."*
                        Ah, but he didn't say the default 2nd argument is 'day'. (He sure implied it, though.)
                        It then goes on to talk of truncation to 'w' weeks, 'month' months, and 'year' years. No mention of 'dd', and every indication that 'day' actually defaults to that day, not the start of the week.
                        I agree; it sounds like it could have been written much better. This is something that's inconsistent and confusing in Oracle, and it would be nice if books explained things like that, rather than making them more confusing.

                        Perhaps Oracle decided to use 'DAY' (and 'D') stand for the NLS_TERRITORY week beacuse none of the other existing format elements do. 'W', 'WW' and 'IW' all mean something other than the NLS_TERRITORY week.
                        • 9. Re: trunc date function not working correctly
                          nick woodward
                          true, you are correct, but to confuse the matter further the examples are displayed as follows:

                          select trunc(to_date('02-jun-2009 13:00','dd-mon-yyyy hh24:mi')) day,
                          trunc(to_date('02-jun-2009','dd-mon-yyyy'), 'w') week,
                          trunc(to_date('02-jun-2009','dd-mon-yyyy'), 'month') month,
                          trunc(to_date('02-jun-2009','dd-mon-yyyy'), 'year') year,
                          from dual;

                          he didn't even bother to show what the 2nd argument for 'day' was, after that paragraph that implied heavily that it was 'day', and not 'dd'.

                          it just seems a bit odd that something so basic wasn't explained particularly well