Last Saturday in month

  • Hi there,

    Would somebody out there know how to calculate the last Saturday in any given month?

    Regards,

    David

     

  • Try this

    SET DATEFIRST 1
    DECLARE @DATE datetime
    DECLARE @EOM datetime
    DECLARE @SAT datetime
    -- set a date to use
    SET @DATE = '15 Jul 2005'
    -- add a month 
    SET @EOM = DATEADD(mm, 1, @DATE)
    -- subtract the days to get last day of month
    
    SET @EOM = DATEADD(dd, -DAY(@EOM), @EOM)
    -- subtract days using weekday number to get Saturday
    
    SET @SAT = DATEADD(dd, -(DATEPART(dw, @EOM))-1, @EOM)
    -- display the result
    SELECT @EOM as [EOM]
        , DATENAME(dw, @EOM) as [EOM_Day]
        , @SAT as [Last_Sat]
        , DATENAME(dw, @SAT) as [Last_day]

     

    --------------------
    Colt 45 - the original point and click interface

  • phillcart, your choice of date was perfect!  The last day of that month is Sunday, hence you need a bit of flow control to prevent going back a full week to get the last Saturday...

    SET DATEFIRST 1

    DECLARE @DATE datetime,

            @EOM datetime,

            @SAT datetime

    -- set a date to use

    SET @DATE = '15 Jul 2005'

    -- add a month

    SET @EOM = DATEADD( mm, 1, @DATE)

    -- subtract the days to get last day of month

    SET @EOM = DATEADD( dd, -DAY( @EOM), @EOM)

    -- subtract days using weekday number to get Saturday

    IF DATENAME( dw, @EOM) = 'Sunday'

         BEGIN

              SET @SAT = DATEADD( dd, - 1, @EOM)

         END

    ELSE

         BEGIN

              SET @SAT = DATEADD( dd, -( DATEPART( dw, @EOM)) - 1, @EOM)

         END

    -- display the result

    SELECT @EOM AS [EOM],

           DATENAME( dw, @EOM) AS [EOM_Day],

           @SAT AS [Last_Sat],

           DATENAME( dw, @SAT) AS [Last_day]

     

    I wasn't born stupid - I had to study.

  • I was thinking about that after I posted the code. Surely it could be done without the flow control. Some sort of logic involving the datepart value maybe ????

     

    --------------------
    Colt 45 - the original point and click interface

  • Probably - but I figured a bulldozer approach was just what it needed...  

    (It probably could be done with a CASE statement...)

     

    I wasn't born stupid - I had to study.

  • The following code makes it possible to get the last weekday for any year and month selected. You can fit this code into a stored procedure with month and year as input parameters.

    DECLARE @month int, @year int, @day int, @date varchar(11), @lastWeekday varchar(11), @teller int

    SET @month = 2  --// input in case of stored procedure

    SET @year = 2002 --// input in case of stored procedure

    SET @day = 22  --// standard, earliest possible last Saturday of month

    SET @date = CONVERT(varchar(4), @year) + '-' + CONVERT(varchar(2),@month) + '-' + CONVERT(VARCHAR(2), @day)

    SET @teller = 0  --// default value

    WHILE  @teller < DATEPART(dd,DATEADD(dd, -DAY( DATEADD( mm, 1, @date)), DATEADD( mm, 1, @date))) - 22 --number of days to endth of month

    BEGIN

     IF DATEPART(dw, @date) = 7 --// thus a Saturday

     BEGIN

      SET @lastWeekday = @date

     END

     SET @Teller = @Teller + 1

     SET @day = @day + 1  --// next day

     SET @date = CONVERT(varchar(4), @year) + '-' + CONVERT(varchar(2),@month) + '-' + CONVERT(VARCHAR(2), @day)

    END

    SELECT 'Last ' + DATENAME(dw,@lastWeekday) + ' of the selected year and month is on ' + CONVERT(VARCHAR(50), CONVERT(DATETIME,@lastWeekday),106)

  • I think it can be even simpler if you're not doing it inside UDF (UDF does not allow SET DATEFIRST). It will work for any valid dates, and you can specify whether you want last Saturday, last Sunday...etc. of the given month.

    /*

    @DW = day of week which to look for (1=Monday!!!)

    @mydate = specifies the month and year (day must be entered, but result is the same regardless of which day it is)

    */

    DECLARE @last_day datetime, @mydate datetime, @DW int

    SET @DW = 6 /*Saturday*/

    SET @mydate = '20051103' /*or GETDATE() or select date you need to work with from some table*/

    /*calculate the last day of the same month*/

    SET @last_day = dateadd(day, -1, dateadd(month, 1, convert(char(6), @mydate, 112) + '01'))

    /*find the last specified day of week before last day (including last day)*/

    SET DATEFIRST @DW

    SELECT @last_day + (1-datepart(dw,@last_day))

    EDIT : should be followed by re-setting the default datefirst value, e.g. SET DATEFIRST 1

  • SET DATEFIRST 1

    DATEADD(month,DATEDIFF(month,0,@DATE)+1,0)

        - 1

        - (CAST(SUBSTRING('2345601',DATEPART(weekday,DATEADD(month,DATEDIFF(month,0,@DATE)+1,0)-1),1) as int))

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Thanks for the prompt replies,

    The reason I ask it to do with daylight savings and GMT. I was looking to apply the following logic, and I believe what you have suggested will help:

    If the date/time is between 16:59:59 on the last Saturday in March and 15:59:59 on the last Saturday in October then add 10:00 hours, otherwise add 11:00 hours.

    Regards,

    David

  • Dunno about all the DST and GMT stuff, but here's a way to find the last Saturday of a month given any date in that month... since it includes time, I imagine you could add the DST and GMT offsets before or after you run the following... no strings, either...

    Also works for doing a whole column in a table... just sub the datetime column name for @Date in the SELECT...

        SET DATEFIRST 7

    DECLARE @Date DATETIME

     SELECT @Date = '07/15/2005'

     SELECT DATEADD(mm,DATEDIFF(mm,0,@Date)+1,0)

           -DATEPART(dw,DATEADD(mm,DATEDIFF(mm,0,@Date)+1,0))

    Adam Mechanic would probably remind you about the need for auxilary calendar tables or at least a numbers table...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Calendar and numbers tables are the best approach!  Do a search on these - RGR'us has a number of postings with those approaches...

     

    I wasn't born stupid - I had to study.

  • Farrell,

    You didn't read far enough down where I said "Adam Mechanic would probably remind you about the need for auxilary calendar tables or at least a numbers table...".  Or were you just confirming what I said?

    I also noticed that the solution you posted didn't use a numbers or calendar table nor even mentioned using them. 

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yeah, yeah,... I know.  Do as I say, not as I do...   

     

    I wasn't born stupid - I had to study.

  • --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply