July 11, 2008 at 9:07 am
Hi,
Can you pls help. First a bit of background info.
We have a history table that contains a snapshot of everything that happened in a weekly period. Our week starts on a Monday and ends on Saturday - so we take this extract from a copy of the DB on Sunday for the week that has just finished and date (Date_Written field) all these new rows as last mondays date (to show that they are data from week commencing last monday).
This dts job can sometimes fail on sunday due to several reasons. We would then have to manually take the extract anytime in the new week (a weekday). Regardless of the exact day that this latest extract is taken in the week, the Date_Written will always be calculated to the same - last week's moondays date. The bit of the script that calculates this date in the stored procedure shown below.
Now to the help needed.
I've just set up a new history table for something else. It's scheduled to run on monday mornings and date all the new rows as that same mondays date (getdate()). But just in case the job fails and i have to re-run it on a Wednesday or say, Friday - how can i get the date to be calculated back to monday.
Many thanks
-- Declare
Declare @DayNoint
Declare @LastMondaydatetime
Declare @LastMonChar char(20)
Declare @MaxDate datetime
-- Calculate LastMondays date
Select @DayNo = DATEPART(weekday, getdate())
Select @DayNo = @DayNo - 1
If @DayNo = 0
Begin
Select @DayNo = 7
End
If @DayNo <= 5
Begin
Select @LastMonday = DATEADD(day, ((@DayNo + 6) * -1), getdate())
End
Else
Begin
Select @LastMonday = DATEADD(day, ((@DayNo - 1) * -1), getdate())
End
Select @LastMonChar = CONVERT(char,@LastMonday)
Select @LastMonChar = SUBSTRING(@LastMonChar,1,12) + '00:00AM'
Select @LastMonday = CONVERT(datetime,@LastMonChar)
July 11, 2008 at 9:16 am
Can you use this?
DECLARE @LastMonDay_date DateTime
Set @LastMonDay_date =
CONVERT(varchar(10),dateadd(day, case(datename(weekday,GETDATE()))
when 'Monday' then 0
when 'Tuesday' then -1
when 'Wednesday' then -2
when 'Thursday' then -3
when 'Friday' then -4
when 'Saturday' then -5
when 'Sunday' then -6
else 0
end ,getdate()),101)
PRINT @LastMonDay_date
July 11, 2008 at 9:48 am
Subtract the day of the week from the current date and then add the number of days from the beginning of the week to the day you want. Assuming you are configured for the week to begin on a Subday:
SELECT DATEADD(Day,-DATEPART(DW,GETDATE())+2,GETDATE())
July 11, 2008 at 11:08 am
You should avoid the other solutions posted on this thread, because they depend on the setting of DATEFIRST or LANGUAGE.
This code shows a simple way to do it.
select
DATE,
Mon = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690)
from
-- Test date
(select DATE=getdate() ) a
Results:
DATE Mon
----------------------- -----------------------
2008-07-11 13:05:15.783 2008-07-07 00:00:00.000
(1 row(s) affected)
You can also use the function on this link to give you the Monday (or any other day of the week) on or before the current day.
Start of Week Function:
July 11, 2008 at 11:13 am
The other thing you can do, which I recommend in any case where you are working with date-sensitive material, is build a calendar table and query against that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2008 at 11:25 am
If you want to build a date table, the function on this link will give you what you need.
Date Table Function F_TABLE_DATE
July 14, 2008 at 7:34 am
Thanks very much for you help on this - much appreciated.
July 15, 2008 at 3:18 pm
just because...
DECLARE @aCurrentDate DATETIME
SET @aCurrentDate = GETDATE()
SELECT DATEADD(dd, -(DATEPART(weekday, @aCurrentDate)+7 - DATEPART(weekday, '1/1/1900'))%7, @aCurrentDate)
July 15, 2008 at 3:45 pm
Daryl, that one won't work if you have DateFirst or Language settings that don't make Sunday = 1 in the week. Too common and too easy to change to use that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 15, 2008 at 4:09 pm
I haven't tested it but with the +7 & modulo against a monday (1/1/1900) it would appear to me to be independent of DATEFIRST type database property changes.
btw: LOVE THE QUOTE!
I've got a google doc shared with some fellow coders title: "processes do not fix stupid!"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply