Best way to identify the first SAT of a month

  • Hello all,

    I have a DTS package that runs on Saturdays and want to come up with a way to determine if a Saturday is the FIRST saturday of a month. What I have is as below:

    ****************************

    declare @first_SAT char(1)

     if(datename(d, getdate())) <7

       begin

     set @first_SAT = 'Y'

       end

    else

       begin 

     set @first_SAT = 'N'

       end

    select @first_SAT as first_SAT

    ******************************

    Do you have any better way to do this? If you do, please share with me!

    Thanks!

     

     

     

     

  • Are you only wanting the DTS job to execute it's steps if it is the first Sat. of the month?  If so, the scheduler will allow you to set your DTS job up to only run on the first Sat. of each month. 

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Johnronwan,

    Nope... that is not what I want. My DTS package will run every Saturday, but on the first SAT it will do a little bit different. I want to identify the first SAT of the month, so I can instruct my DTS package to follow this path on the first Saturday and go the other path on other Saturdays.

    Thanks!

     

  • What you have there works fine; however, I have a question for you.  If the first day of the month is a Sunday, then the first Saturday is the 7th.  In your code you specify < 7, wouldn't that return an 'N' in the case of the first Saturday of the month being on the 7th of that month?  I'm thinking you need a <= for that comparison.  If I'm wrong in that one, sorry about that.  Good luck.

    Mark

  • Mark,

    Good catch! I should have it as <=7 instead.

    Thanks!

     

  • I think you also need to consider the @@DATEFIRST setting which determines which day of the week is considered DAY 1.

    For example US English Sunday is Day 7. For some reason most SQL installations I come across (in the UK) are set to US English complete with US English Keyboard locales on UK English keyboards.

    Don't know why this is but it is sodding annoying.

  • Hi,

    I keep trying to find someone to give this to because I spent a fair amout of time on it; only to find out someone up the chain was handling the problem. I think you might be able to do something like I did to find the first Sunday of a given month for calculating daylight savings time. There is probably some simple one line razzle dazzle solution but it didn't pop into my head.

    Teague

     

     

    CREATE function dbo.fn_UtcToCentralTime(@DateToConvert Char(19))

    -- 11-12-2004 Teague Byrd

    -- Takes a UTC date stored as char(19) e.g. '2004-11-12 13:25:00'   and converts it to Central time.

    --  Daylight Saving time is taken into account in the calculation.

    --

    returns smalldatetime

    as

    begin

     

    declare @YearPart char(4)

    declare @FirstSundayInApril smalldatetime

    declare @LastSundayInOctober smalldatetime

    declare @Offset smallint

    declare @CentralTime smalldatetime

     

    set @YearPart = substring(@DateToConvert,1,4)

     

    --Determine the beginning and ending dates for Daylight Saving Time for the year being converted

    --This should work for any date including dates in the past and future that can be stored as a SQLServer SmallDatetime

     

    select @FirstSundayInApril =

      case

       when datepart(dw,@YearPart + '-04-01') = 1 then @YearPart + '-04-01 02:00:00'

       when datepart(dw,@YearPart + '-04-02') = 1 then @YearPart + '-04-02 02:00:00'

       when datepart(dw,@YearPart + '-04-03') = 1 then @YearPart + '-04-03 02:00:00'

       when datepart(dw,@YearPart + '-04-04') = 1 then @YearPart + '-04-04 02:00:00'

       when datepart(dw,@YearPart + '-04-05') = 1 then @YearPart + '-04-05 02:00:00'

       when datepart(dw,@YearPart + '-04-06') = 1 then @YearPart + '-04-06 02:00:00'

       when datepart(dw,@YearPart + '-04-07') = 1 then @YearPart + '-04-07 02:00:00'

    end

    select @LastSundayInOctober =

      case

       when datepart(dw,@YearPart + '-10-31') = 1 then @YearPart + '-10-31 02:00:00'

       when datepart(dw,@YearPart + '-10-30') = 1 then @YearPart + '-10-30 02:00:00'

       when datepart(dw,@YearPart + '-10-29') = 1 then @YearPart + '-10-29 02:00:00'

       when datepart(dw,@YearPart + '-10-28') = 1 then @YearPart + '-10-28 02:00:00'

       when datepart(dw,@YearPart + '-10-27') = 1 then @YearPart + '-10-27 02:00:00'

       when datepart(dw,@YearPart + '-10-26') = 1 then @YearPart + '-10-26 02:00:00'

       when datepart(dw,@YearPart + '-10-25') = 1 then @YearPart + '-10-25 02:00:00'

    end

     

    --Calculate the offset for the Central timezone

    if @DateToConvert > @FirstSundayInApril and @DateToConvert < @LastSundayInOctober

      set @Offset = 5  --Central daylight saving time

    else

      set @Offset = 6  --Central standard time

     

    --Subtract the offset from the given UTC date

    set @CentralTime = dateadd(hh,-@Offset,@DateToConvert)

     

    -- Return the calculated Central Time for the given UTC date

    Return (@CentralTime)

     

    end

     

  • If your job only runs on Saturday, then you've done it the most efficient way.

    --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 8 posts - 1 through 7 (of 7 total)

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