June 24, 2011 at 1:38 pm
Hi, I m trying to write a query to find out all the data in the current week. Like, If I run query on any day of the week, I wanted get all the data from Monday 12.01 AM. Can someone help me in this?
Thanks a lot
June 24, 2011 at 1:51 pm
This is a lot easier if we had some sample schema and test data to show you proven code. See the first link in my signature for help on that.
Generically: You're looking at using SET DATEFIRST to make sure you start on the right day, DATEPART( dw) for your day of the week, subtracting that from the current date, manipulating the time component of GETDATE() a bit with DATEADD/DATEDIFF, and then using that as a parameter in your WHERE clause.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 24, 2011 at 1:54 pm
This is based on a week from Monday 12:00 AM to Sunday 11:59:59.999PM:
SELECT
FROM YourTable
WHERE YourColumn > = DATEADD(wk,DATEDIFF(wk,0, GETDATE() -1 ),0)
June 24, 2011 at 2:07 pm
@Craig: I strongly vote against any method involving SET DATEFIRST and/or DATEPART(dw)
One of the (undesired?) side effects: once you have a specific SET DATEFIRST in your batch, any following SET LANGUAGE won't change the DATEFIRST value anymore (but it will without the specific DATEFIRST setting earlier in that batch).
This might sound like a very specific scenario. But once you got trapped by it, it'll take a while to find and fix it accordingly....
June 24, 2011 at 2:12 pm
Thanks LutZ. It works perfect
June 24, 2011 at 2:39 pm
LutzM (6/24/2011)
@Craig: I strongly vote against any method involving SET DATEFIRST and/or DATEPART(dw)One of the (undesired?) side effects: once you have a specific SET DATEFIRST in your batch, any following SET LANGUAGE won't change the DATEFIRST value anymore (but it will without the specific DATEFIRST setting earlier in that batch).
This might sound like a very specific scenario. But once you got trapped by it, it'll take a while to find and fix it accordingly....
I'm not necessarily sure I'd want SET LANGUAGE to be changing my DATEFIRST once I've specifically set it anyway, so your concern is actually a benefit to me. I'll keep it in mind though.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 24, 2011 at 2:42 pm
Craig Farrell (6/24/2011)
...I'm not necessarily sure I'd want SET LANGUAGE to be changing my DATEFIRST once I've specifically set it anyway, so your concern is actually a benefit to me. I'll keep it in mind though.
Sounds like a classic "it depends" then 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply