date functions
-
how can i get a week worth of data, for this query week starts on wednesday and ends next tuesday.
say, query is executed on thursday it should show data from wednesday to next tuesday (for the week)
and so on..
-
You can do it using DATEADD and DATEDIFF functions, but how exactly, that depends on what should happen when you run the query on Wednesday (or with a day that is Wednesday as parameter): return last week data, or the data beginning this Wednesday. It is independent on server and local settings.
DECLARE @mydate DATETIME
SET @mydate='20070919'
/*returns this Wednesday*/
SELECT DATEADD(day, (DATEDIFF (day, '20000105', @mydate) / 7) * 7, '20000105')
/*returns last Wednesday*/
SELECT DATEADD(day, (DATEDIFF (day, '20000106', @mydate) / 7) * 7, '20000105')
Choose the one you need, add similar formula for the other day you need (next Tuesday) - and that's it. Just be careful and choose the auxiliary dates (here 20000105, 20000106) far back enough, so that it does not happen that you need to work with @mydate older than these. It will not work properly in such case.
-
This will also work:
declare
@saveDateFirst tinyint,
@curDate datetime,
@LowerBound datetime,
@UpperBound datetime;
set @saveDateFirst = @@datefirst
set @curDate = '2007-09-18T09:31:34' -- getdate()
set datefirst 3
select @LowerBound = dateadd(dd,datediff(dd,0,dateadd(dd, -1 * (datepart(dw, @curDate) - 1) % 7, @curDate)),0), @UpperBound = dateadd(dd,datediff(dd,0,dateadd(dd, -1 * (datepart(dw, @curDate) - 1) % 7, @curDate)) + 7, 0)
set datefirst @saveDateFirst
select @curDate, @LowerBound, @UpperBound -- This is just to show the values after the computations are completed
The WHERE clause would look like this:
WHERE yourDateColumn >= @LowerBound and yourDateColumn < @UpperBound
-
I realized after posting I could remove the modulo calculation (%). This produces the same result:
declare @saveDateFirst tinyint,
@curDate datetime,
@LowerBound datetime,
@UpperBound datetime;
set @saveDateFirst = @@datefirst
set @curDate = '2007-09-18T09:31:34' -- getdate()
set datefirst 3
select @LowerBound = dateadd(dd,datediff(dd,0,dateadd(dd, -1 * (datepart(dw, @curDate) - 1), @curDate)),0), @UpperBound = dateadd(dd,datediff(dd,0,dateadd(dd, -1 * (datepart(dw, @curDate) - 1), @curDate)) + 7, 0)
set datefirst @saveDateFirst
select @curDate, @LowerBound, @UpperBound -- This is just to show the values after the computations are completed
-
Well, I admit that I didn't have patience to analyze Lynn's code, but it does not look simpler than the code I posted, and it uses SET DATEFIRST... so I would still prefer mine, which works well and without any messing with settings - universal is better IMHO.
-
Just another way to do it. One benefit, you don't have to know a date in the past that falls on the day you want to start your week on or worry about going back far enough to cover the period of time you may report over. This is a universal method in SQL Server (unless they take away the DATEFIRST capability. By capturing the current setting for datefirst, changing it to the day of the week requested, and then resetting it when done, it should work anywhere.
I would think we are here to provide additional alternatives if available and let the users decide which way to go. We all have our preferences on how to do things, and I am open to other alternatives as well. I just wanted to see if i could find a way that was not dependent on having a date from the past.
-
I think this does what you want, and you don’t have to worry about the setting of DATEFIRST.
This has links to various date function:
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
select
DATE,
-- Returns First Wednesday on or before a.DATE
StartOfWeek = dateadd(dd,(datediff(dd,-53688,a.DATE)/7)*7,-53688),
-- Returns First Tuesday after beginning of week
EndOfWeek = dateadd(dd,((datediff(dd,-53688,a.DATE)/7)*7)+6,-53688)
from
(
select date = convert(datetime,'2007-09-18') union all
select date = convert(datetime,'2007-09-19') union all
select date = convert(datetime,'2007-09-20') union all
select date = convert(datetime,'2007-09-21') union all
select date = convert(datetime,'2007-09-22') union all
select date = convert(datetime,'2007-09-23') union all
select date = convert(datetime,'2007-09-24') union all
select date = convert(datetime,'2007-09-25')
) a
DATE StartOfWeek EndOfWeek
----------------------- ----------------------- -----------------------
2007-09-18 00:00:00.000 2007-09-12 00:00:00.000 2007-09-18 00:00:00.000
2007-09-19 00:00:00.000 2007-09-19 00:00:00.000 2007-09-25 00:00:00.000
2007-09-20 00:00:00.000 2007-09-19 00:00:00.000 2007-09-25 00:00:00.000
2007-09-21 00:00:00.000 2007-09-19 00:00:00.000 2007-09-25 00:00:00.000
2007-09-22 00:00:00.000 2007-09-19 00:00:00.000 2007-09-25 00:00:00.000
2007-09-23 00:00:00.000 2007-09-19 00:00:00.000 2007-09-25 00:00:00.000
2007-09-24 00:00:00.000 2007-09-19 00:00:00.000 2007-09-25 00:00:00.000
2007-09-25 00:00:00.000 2007-09-19 00:00:00.000 2007-09-25 00:00:00.000
(8 row(s) affected)
-
Sure Lynn, I wasn't trying to tell that your method is wrong or anything like that... just that I personally prefer to avoid SET DATEFIRST. It is always good to provide alternatives!
-
Nicely done (as usual), Michael. Simple, straight forward, nasty fast...
--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) -
Jeff, I'm a little unsure what you're trying to say there.
-
I knew this day would come...Jeff's avatar has become self aware and is now gobbling up all replies to his posts. soon it will take over and launch a preemptive nuclear strike....<terminator music in the background>.----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Wow, Michael... not sure what happened there. I've seen that before (post gobbling others) but not when I've posted. Didn't even show what I posted. Matt might be right... might be prepping for a PNS against bad code
<insert Jaw's theme song here>What I was trying to say about the code you posted is that, as usual, it's pretty much spot on... simple, does the job well, and nasty fast.
--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) -
Well, with praise like that, I'm glad I asked. Thanks.
-
Goes to show how important coffee is to the IT industry. Last night - I spent most of an hour scratching my head, wondering what new-fangled item Jeff was referring to as PNS.....
Now - with coffee drip on full tilt....hehe....----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply