Best way to identify the first SAT of a month

Question

Best way to identify the first SAT of a month

Somebody

SSC Eights!

Points: 928
More actions
December 22, 2005 at 2:26 pm

#92532

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!

Viewing 8 posts - 1 through 7 (of 7 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

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

Somebody SSC Eights! Points: 928 More actions · Answer 2

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!

Mark Yelton SSCommitted Points: 1570 More actions · Answer 3

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

Somebody SSC Eights! Points: 928 More actions · Answer 4

Mark,

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

Thanks!

David.Poole SSC Guru Points: 76001 More actions · Answer 5

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.

LinkedIn Profile

Teague Byrd Right there with Babe Points: 752 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

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)