December 22, 2005 at 2:26 pm
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!
December 22, 2005 at 2:32 pm
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.
December 22, 2005 at 2:36 pm
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!
December 22, 2005 at 3:06 pm
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
December 22, 2005 at 3:13 pm
Mark,
Good catch! I should have it as <=7 instead.
Thanks!
December 22, 2005 at 4:04 pm
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.
December 23, 2005 at 2:20 pm
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
December 24, 2005 at 11:52 am
If your job only runs on Saturday, then you've done it the most efficient way.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply