January 15, 2010 at 10:32 am
Hi Guys,
I have a job that needs to run every Sunday and it will grab a data created from Friday @ 7:00PM of the previous week, through Friday @ 7:00PM of the current week. I'm having a real heck of a time trying to figure out how to code the date functions for this. Here are the basic ones I created, so if someone could help me tweak them to look for the range I need i would GREATLY apprciate it.
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
Thanks!
-Code
January 15, 2010 at 11:17 am
Go to this posting by Lynn Pettis for almost every useful datetime T-SQL examples.
https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx[/url]
I am sure you will find what you need in the extensive listing.
January 15, 2010 at 11:34 am
Thanks for the link. I already am familiar with all those, it's the time portion im struggling with.
January 15, 2010 at 11:50 am
Do the two select statements in your original post get you the correct starting and ending dates (not asking about time)?
January 15, 2010 at 11:54 am
Hi Lynn,
This is what I have in the job as of right now.
SET @start_dt = (SELECT dateadd(day,datediff(day,0,GetDate())- 9,0)) --if RUNS SUNDAY, THIS GRABS FRIDAY @ 12:00AM OF THE PREVIOUS WEEK
SET @end_dt = (SELECT dateadd(day,datediff(day,0,GetDate())- 2,0)) --if RUNS SUNDAY, THIS GRABS FRIDAY @ 12:00AM
Designating a specific time to the date function is what I have been struggling with.
January 15, 2010 at 11:58 am
Ready to smack yourself on the forehead??
SET @start_dt = (SELECT dateadd(hh, 19, dateadd(day,datediff(day,0,GetDate())- 9,0)))
SET @end_dt = (SELECT dateadd(hh, 19, dateadd(day,datediff(day,0,GetDate())- 2,0)))
January 15, 2010 at 12:01 pm
OMFG! *smacks forehead with sledge hammer*
Lynn, you rock!! Thanks!
January 15, 2010 at 12:04 pm
Welcome, you are. Any time.
January 15, 2010 at 3:11 pm
Lynn Pettis (1/15/2010)
Ready to smack yourself on the forehead??
SET @start_dt = (SELECT dateadd(hh, 19, dateadd(day,datediff(day,0,GetDate())- 9,0)))
SET @end_dt = (SELECT dateadd(hh, 19, dateadd(day,datediff(day,0,GetDate())- 2,0)))
And, just to make things a bit simpler you can drop the select:
SET @start_dt = dateadd(hh, 19, dateadd(day,datediff(day,0,GetDate())- 9,0))
SET @end_dt = dateadd(hh, 19, dateadd(day,datediff(day,0,GetDate())- 2,0))
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 15, 2010 at 3:21 pm
Jeffrey Williams-493691 (1/15/2010)
Lynn Pettis (1/15/2010)
Ready to smack yourself on the forehead??
SET @start_dt = (SELECT dateadd(hh, 19, dateadd(day,datediff(day,0,GetDate())- 9,0)))
SET @end_dt = (SELECT dateadd(hh, 19, dateadd(day,datediff(day,0,GetDate())- 2,0)))
And, just to make things a bit simpler you can drop the select:
SET @start_dt = dateadd(hh, 19, dateadd(day,datediff(day,0,GetDate())- 9,0))
SET @end_dt = dateadd(hh, 19, dateadd(day,datediff(day,0,GetDate())- 2,0))
True, that is.
January 15, 2010 at 5:11 pm
Looks like you may already have what you need, but since I'd just a couple of weeks ago had a similar case, I figure I'll share it. The main difference in function is that alhough our weekly job will be run on Saturdays, I wanted the code to be resilient enough to work without intervention if it were delayed a day or two. We also wanted to be able to override the default range start and end. We didn't need clock times in the range limits as we are dealing only with upon which business date a transaction was processed.
The trick to finding the date of the most recent Friday was in using the modulo 7 operator to produce an acurate number of days, finding the most recent Saturday even if that's today, then subtracting 1. Note that as with any code using DatePart to find the day of the week, it depends on the DateFirst setting, in this case the US default of '7'.
This stored procedure was made to be used by an SSIS package, which will always pass input parameters, even if they're empty strings, and had other processing not related to the date range. I've trimmed it down to just that.
If you put your hammer down for a moment, you can add the +19 hours to the output yourself;-).
CREATE procedure [dbo].[usp_SetRunParms]
@RangeStart varchar(20)
,@RangeEnd varchar(20)
,@RangeLength varchar(3)
AS
Declare @Now datetime
Declare @today datetime
Set @now = getdate()
set @today = DateAdd(dd, 0,Datediff(dd, 0, @now))
Declare @StartDate datetime
Declare @EndDate datetime
set @RangeLength = case when @RangeLength = '' then '7' else @RangeLength end
Select @endDate
= case when @RangeEnd = ''
-- For the default End date, use Modulo 7 so we get zero (0) for Saturday & actual DOW for other days.
-- This is the number of days since the most recent Saturday including today (if Sat.).
-- Then, subtract 1 more day to find most recent Friday before today (even if today is Fri.).
then @today - (datepart(dw,@today) %7) -1
else convert(datetime,@RangeEnd)
end
Select @StartDate
= case when @RangeStart = ''
--Default Startdate is @RangeLength days back from calcualated @endDate (probably seven)
then @endDate - convert(int,@RangeLength)
else convert(datetime,@RangeStart)
end
Select @startDate as StartDate
,@EndDate as EndDate
To run, specify all three parameters every time:
usp_SetRunParms '','','14' --get two weeks
go
usp_SetRunParms '','20100101','' -- get data for week ending 1/1/2010
go
usp_SetRunParms '20100111','20100112','' -- get two days' data
go
StartDate EndDate
----------------------- -----------------------
2009-12-25 00:00:00.000 2010-01-08 00:00:00.000
(1 row(s) affected)
StartDate EndDate
----------------------- -----------------------
2009-12-25 00:00:00.000 2010-01-01 00:00:00.000
(1 row(s) affected)
StartDate EndDate
----------------------- -----------------------
2010-01-11 00:00:00.000 2010-01-12 00:00:00.000
(1 row(s) affected)
---
edit: add example of run
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply