1 2 Previous Next 26 Replies Latest reply: May 2, 2012 1:41 AM by indra budiantho Go to original post RSS
      • 15. Re: List of Saturday
        BobLilly
        Note that NEXT_DAY will return the following week when the input date falls on the requested day, so your SQL will miss the first Saturday in months that start on a Saturday. For example, NEXT_DAY(date '2012-09-01','Sat') will return 2012-09-08. You'll have to use the last day of the prior month with NEXT_DAY to get the first Saturday of the current month.
        select level as no, next_day(trunc(date '2012-09-15','mon')-1,'sat')+7*(level-1) from dual
        connect by next_day(trunc(date '2012-09-15','mon')-1,'sat')+7*(level-1) <= last_day(date '2012-09-15')
        ;
        Regards,
        Bob
        • 16. Re: List of Saturday
          chris227
          yet another reason to use a solution without next day, like the one given from me ;-)
          • 17. Re: List of Saturday
            Billy~Verreynne
            Recursion is far more beautiful... I grew up on Logo and Lisp. ;-)
            • 18. Re: List of Saturday
              chris227
              Billy  Verreynne  wrote:
              Recursion is far more beautiful... I grew up on Logo and Lisp. ;-)
              Agreed, though i had collegues who tend to rewrite my recursions in regards of readability and understandability.
              I remeber one of my first lectures in university when the professor 'surprised' us with haskell ...

              But 'connect by' is recursive also, isnt it?

              However, the point here was if there is a reasonable alternative to next day and i think there is.
              • 19. Re: List of Saturday
                Sven W.
                Billy  Verreynne  wrote:
                chris227 wrote:

                one question on this. I would consider the use of 'Saturday' as a hardcode literal. As this relies on the nls settings i personally would consider a solution that works without this.
                Am i wrong ?
                The manual states:
                "+The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version.+"

                And in my part of the world, Saturday is just that, and always spelled like that - could not say that I care much of what they use or do not use in other parts of the world (unless I get paid for that, and paid in hard cash in my local currency). :-)
                Funnily the literal in NEXT_DAY seems not to rely on nls settings. That explains also, why there is no third nls parameter to the function.
                alter session set nls_date_language=GERMAN;
                select next_day(sysdate,'Saturday'), to_char(next_day(sysdate,'Saturday'),'Day')  from dual;
                
                05.05.12     Samstag   
                Therefore using SATURDAY as a literal is acceptable. Problem might be if the end user is allowed to choose the weekday by some parameter. This has to be translated into english, if the users nls settings are different.

                Tested on a oracle 11.2.0.1 EE.

                Edited by: Sven W. on Apr 30, 2012 8:13 PM
                • 20. Re: List of Saturday
                  Billy~Verreynne
                  chris227 wrote:

                  Agreed, though i had collegues who tend to rewrite my recursions in regards of readability and understandability.
                  I remeber one of my first lectures in university when the professor 'surprised' us with haskell ...
                  Before my time at university. Back then it was Turbo Pascal, Logo, Pascal and Lisp (on mainframes and minis) and some ProLog.. The most common languages in use by business were Cobol, Fortran and C.. which made for an interesting career starting out as a programmer. :-)

                  Haskell sounds quite interesting though. I would love to learn a new language that I can use professionally - something a bit removed from the object and procedural norms of Java and C# (PL/SQL too) and others.

                  It gets boring.
                  But 'connect by' is recursive also, isnt it?
                  Not sure if it a pure self-similar reference.
                  However, the point here was if there is a reasonable alternative to next day and i think there is.
                  Well, if boring code is the intention - instead of elegance through simplicity using recursion (and the literal for the day of the week would in any way be a bind variable in a production app - the value of which the caller needs to determine using NLS settings or whatever). So no, you've not convinced me - not that it makes any difference anyway.. ;-)
                  • 21. Re: List of Saturday
                    Sven W.
                    Billy  Verreynne  wrote:
                    Using SQL recursion (available in 11gr2 - not sure about 11gr1 as I've never used it):
                    SQL> with saturday(week,day) as(
                    2          select
                    3                  1,
                    4                  cast( next_day(trunc(sysdate),'Saturday') as date )
                    5          from    dual
                    6          union all
                    7          select
                    8                  week+1,
                    9                  cast( next_day(day,'Saturday') as date )
                    10          from    saturday
                    11          where   week < 12
                    12  )
                    13  select
                    14          week,
                    15          to_char( day, 'yyyy/mm/dd Day' ) as day
                    16  from    saturday
                    17  order by 1;
                    I changed Billys query a littly and got some strange results. Can somebody explain what happend? Do you get the same result (11.2.0.1)?
                    with saturday(week,dy) 
                        as(  select   1 w,
                                      cast(next_day(last_day(sysdate),'Saturday') as date) d
                              from    dual v1
                              union all
                              select v2.week+1,
                                     v2.dy+7
                              from   saturday v2
                              where  v2.week <= 4
                          )
                      select
                              v3.week,
                              to_char( v3.dy, 'yyyy/mm/dd Day' ) as day
                      from    saturday v3
                      where   trunc(sysdate,'MM')=trunc(v3.dy,'MM')
                      order by v3.week;
                    
                    results
                    -------------------------------
                    2     2012/04/28 Samstag   
                    3     2012/04/21 Samstag   
                    4     2012/04/14 Samstag   
                    5     2012/04/07 Samstag   
                    Two things that bother me.
                    1) The recursion was done backwards. Meaning the date got substracted by 7 for each recursive row.
                    2) The with clause returned 5 rows, even if the condition is week <= 4.

                    Ok I understood the second problem, while typing this. I return week+1 for the last row that satisfies the condition. Which equals 5.

                    Edited by: Sven W. on Apr 30, 2012 8:49 PM
                    • 22. Re: List of Saturday
                      chris227
                      typo?
                      select * from v$version
                      
                      BANNER
                      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                      PL/SQL Release 11.2.0.3.0 - Production
                      CORE 11.2.0.3.0 Production
                      TNS for Linux: Version 11.2.0.3.0 - Production
                      NLSRTL Version 11.2.0.3.0 - Production
                      
                      with saturday(week,dy) 
                          as(  select   1 w,
                                        cast(next_day(last_day(sysdate),'Saturday') as date) d
                                from    dual v1
                                union all
                                select v2.week+1,
                                       v2.dy+7
                                from   saturday v2
                                where  v2.week <= 4
                            )
                        select
                                v3.week,
                                to_char( v3.dy, 'yyyy/mm/dd Day' ) as day
                        from    saturday v3
                        where   trunc(sysdate,'MM')=trunc(v3.dy,'MM')
                        order by v3.week
                      
                      no data found
                      
                      with saturday(week,dy) 
                          as(  select   1 w,
                                        cast(next_day(last_day(sysdate),'Saturday') as date) d
                                from    dual v1
                                union all
                                select v2.week+1,
                                       v2.dy+7
                                from   saturday v2
                                where  v2.week <= 4
                            )
                        select
                                v3.week,
                                to_char( v3.dy, 'yyyy/mm/dd Day' ) as day
                        from    saturday v3
                        where   trunc(sysdate,'MM')<=trunc(v3.dy,'MM') --changed
                        order by v3.week
                      
                      WEEK     DAY
                      1     2012/05/05 Saturday
                      2     2012/05/12 Saturday
                      3     2012/05/19 Saturday
                      4     2012/05/26 Saturday
                      5     2012/06/02 Saturday 
                      Edited by: chris227 on 30.04.2012 12:05
                      • 23. Re: List of Saturday
                        Billy~Verreynne
                        Sven W. wrote:

                        Can somebody explain what happend? Do you get the same result (11.2.0.1)?
                        Yes, same results (I removed the mail SQL's where clause to see all the rows unfiltered).

                        Here's a combination of the method you used (that goes backwards in time despite adding 7 days), and a working method.
                        SQL> with saturday(week,cnt,day1,day2) as(
                          2          select
                          3                  1,
                          4                  7,
                          5                  cast( sysdate as date ),
                          6                  cast( sysdate as date )
                          7          from    dual
                          8          union all
                          9          select
                         10                  week + 1,
                         11                  cnt+7,
                         12                  cast(sysdate + cnt as date),
                         13                  cast(day2 + 7 as date)
                         14          from    saturday
                         15          where   week <= 4
                         16  )
                         17  select
                         18          week,
                         19          cnt,
                         20          to_char( day1, 'yyyy/mm/dd Day' ) as day1,
                         21          to_char( day2, 'yyyy/mm/dd Day' ) as day2
                         22  from       saturday
                         23  order by week;
                        
                              WEEK        CNT DAY1                 DAY2
                        ---------- ---------- -------------------- --------------------
                                 1          7 2012/04/30 Monday    2012/04/30 Monday
                                 2         14 2012/05/07 Monday    2012/04/23 Monday
                                 3         21 2012/05/14 Monday    2012/04/16 Monday
                                 4         28 2012/05/21 Monday    2012/04/09 Monday
                                 5         35 2012/05/28 Monday    2012/04/02 Monday
                        {code}
                        
                        I'm not exactly sure what is happening here with the last date running backwards... but I have a suspicion or two. But it is getting late here, and tomorrow is Worker's Day which means no more thinking about SQL and recursion until Wednesday. ;-)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                        • 24. Re: List of Saturday
                          AlanWms
                          Another solution which doesn't use NLS, at least I don't think it does:
                          with data as
                          (
                          select to_date('01/02/2012','MM/DD/YYYY') as testdate from dual
                          )
                          
                          select level, trunc(testdate,'MM') + level - 1 as saturdays
                          from data
                          where mod(to_char(trunc(testdate,'MM') + level - 1,'J'),7) = 5
                          connect by trunc(testdate,'MM') + level - 1 < add_months(trunc(testdate,'MM'),1)
                          ;
                          This uses the fact that the Julian date for Saturdays is always 5 mod 7.
                          • 25. Re: List of Saturday
                            Billy~Verreynne
                            Decided to add string columns in the recursion to show the calculation - and why, despite incrementing the date, the date runs backwards.

                            Seems even weirder now...
                            // with the added string columns showing the calculation for day2, 
                            // the day2 column value increases correctly
                            SQL> with saturday(week,cnt,day1,day2,calc1,calc2) as(
                              2          select
                              3                  1,
                              4                  7,
                              5                  cast( trunc(sysdate) as date ),
                              6                  cast( trunc(sysdate) as date ),
                              7                  cast( trunc(sysdate) as varchar2(4000) ),
                              8                  cast( trunc(sysdate) as varchar2(4000) )
                              9          from    dual
                             10          union all
                             11          select
                             12                  week + 1,
                             13                  cnt+7,
                             14                  cast(trunc(sysdate) + cnt as date),
                             15                  cast(day2 + 7 as date),
                             16                  cast(trunc(sysdate)||' + '||cnt as varchar2(4000) ),
                             17                  cast(day2||' + 7'  as varchar2(4000) )
                             18          from    saturday
                             19          where   week <= 4
                             20  )
                             21  select
                             22          week,
                             23          cnt,
                             24          to_char( day1, 'yyyy/mm/dd Day' ) as day1,
                             25          calc1,
                             26          to_char( day2, 'yyyy/mm/dd Day' ) as day2,
                             27          calc2
                             28  from       saturday
                             29  order by week;
                            
                                  WEEK        CNT DAY1                 CALC1                          DAY2                 CALC2
                            ---------- ---------- -------------------- ------------------------------ -------------------- ------------------------------
                                     1          7 2012/05/02 Wednesday 2012/05/02 00:00:00            2012/05/02 Wednesday 2012/05/02 00:00:00
                                     2         14 2012/05/09 Wednesday 2012/05/02 00:00:00 + 7        2012/05/09 Wednesday 2012/05/02 00:00:00 + 7
                                     3         21 2012/05/16 Wednesday 2012/05/02 00:00:00 + 14       2012/05/16 Wednesday 2012/05/09 00:00:00 + 7
                                     4         28 2012/05/23 Wednesday 2012/05/02 00:00:00 + 21       2012/05/23 Wednesday 2012/05/16 00:00:00 + 7
                                     5         35 2012/05/30 Wednesday 2012/05/02 00:00:00 + 28       2012/05/30 Wednesday 2012/05/23 00:00:00 + 7
                            
                            // remove the string columns and the day2 column value
                            // now suddenly decreases - kind of like the wave function
                            // collapsing as the observer's interaction with the experiment
                            // has now changed
                            SQL> with saturday(week,cnt,day1,day2) as(
                              2          select
                              3                  1,
                              4                  7,
                              5                  cast( trunc(sysdate) as date ),
                              6                  cast( trunc(sysdate) as date )
                              7          from    dual
                              8          union all
                              9          select
                             10                  week + 1,
                             11                  cnt+7,
                             12                  cast(trunc(sysdate) + cnt as date),
                             13                  cast(day2 + 7 as date)
                             14          from    saturday
                             15          where   week <= 4
                             16  )
                             17  select
                             18          week,
                             19          cnt,
                             20          to_char( day1, 'yyyy/mm/dd Day' ) as day1,
                             21          to_char( day2, 'yyyy/mm/dd Day' ) as day2
                             22  from       saturday
                             23  order by week;
                            
                                  WEEK        CNT DAY1                 DAY2
                            ---------- ---------- -------------------- --------------------
                                     1          7 2012/05/02 Wednesday 2012/05/02 Wednesday
                                     2         14 2012/05/09 Wednesday 2012/04/25 Wednesday
                                     3         21 2012/05/16 Wednesday 2012/04/18 Wednesday
                                     4         28 2012/05/23 Wednesday 2012/04/11 Wednesday
                                     5         35 2012/05/30 Wednesday 2012/04/04 Wednesday
                            
                            SQL>
                            Oracle bug? Or are we missing something obvious (or perhaps no so obvious) in how SQL recursion works?
                            • 26. Re: List of Saturday
                              indra budiantho
                              recursive..tx
                              1 2 Previous Next