This discussion is archived
1 2 Previous Next 26 Replies Latest reply: May 1, 2012 11:41 PM by indra budiantho Go to original post RSS
  • 15. Re: List of Saturday
    BobLilly Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    yet another reason to use a solution without next day, like the one given from me ;-)
  • 17. Re: List of Saturday
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Recursion is far more beautiful... I grew up on Logo and Lisp. ;-)
  • 18. Re: List of Saturday
    chris227 Guru
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    recursive..tx
1 2 Previous Next

Legend

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