Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Previous week date range


Previous week date range

Author
Message
Rob-350472
Rob-350472
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 684
Right, this is prooving to be a right headache, as many date related things in SQL seem to be for some reason:

The 'last week' right now is:

Monday 2nd March
Friday 6th March

So, based on todays date, or indeed ANY date this week I need to write something which basically equates to:

AND communication_date >= Monday 2nd March
AND communication_date <= Friday 2nd March

This would be the same for ANY day this week

I've been trying for quite some time and had a good look around and I can't seem to find anything which would fit the bill.
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3264 Visits: 11771
select
*
from
MyTable
where
-- Date greater than or equal to Monday of last week
MyDate >= dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-7,'17530101')
and
-- Date before Saturday of last week
Mydate < dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-2,'17530101')



Rob-350472
Rob-350472
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 684
Wow, that's mental - never seen the whole '17530101' earliest date type thing before.

Very useful - just wish I had asked before I spent quite a chunk of the afternoon trying to solve it!

thanks very much!
Luke L
Luke L
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2686 Visits: 6103
Michael any reason why you like 17530101 as opposed to 0? I'd never really thought to use the 17530101 like you are doing, but I picked up the use of 0 for doing the same thing from Gregory Larsen's article on databasejournal.com...

http://www.databasejournal.com/features/mssql/article.php/3076421/Examples-of-how-to-Calculate-Different-SQL-Server-Dates.htm

peitech you may want to check it out as it includes a number of examples for calculating just about any date you need and has the added benefit of being wicked fast...

-Luke.

To help us help you read this

For better help with performance problems please read this
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24255 Visits: 37978
Check out the following code and see if it does what you need:


select getdate(), dateadd(wk, datediff(wk, 0, getdate()) - 1, 0), dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 4



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3264 Visits: 11771
Luke L (3/11/2009)
Michael any reason why you like 17530101 as opposed to 0? I'd never really thought to use the 17530101 like you are doing, but I picked up the use of 0 for doing the same thing from Gregory Larsen's article on databasejournal.com...

http://www.databasejournal.com/features/mssql/article.php/3076421/Examples-of-how-to-Calculate-Different-SQL-Server-Dates.htm

peitech you may want to check it out as it includes a number of examples for calculating just about any date you need and has the added benefit of being wicked fast...

-Luke.


I used 17530101 because the code I posted for the first day of the week would not return the correct start of week date for dates before 0 (19000101). Since there is no SQL Server datetime before 17530101, that isn't a problem.

The code from Gregory Larsen's article has a problem with dates before 19000101. When I post code solutions online, I try to give the most general solution possible, as opposed to code that only works for specific ranges of data. Notice how the code from the Gregory Larsen article fails for 18991231.
select
[MVJ First Day of Week] =
dateadd(dd,((datediff(dd,'17530101',a.DT)/7)*7),'17530101'),
[GL First Day of Week] =
dateadd(wk,datediff(wk,0,DT),0)
from
(
select DT = convert(datetime,'18991231')
) a

Results:
MVJ First Day of Week GL First Day of Week
----------------------- -----------------------
1899-12-25 00:00:00.000 1900-01-01 00:00:00.000





Plenty of other info for working with SQL Server datetime on this link:
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762


Code for start of week on this link:
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
Luke L
Luke L
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2686 Visits: 6103
Thanks for the excellent reply and the links, I'll have to dig into them a bit later this week.

Never really thought about using the dateadd/datediff functionality to get to dates prior to 1900 as I've always been asked to support last week, last quarter type of information. Good to know to look out for it on older dates though.

-Luke.

To help us help you read this

For better help with performance problems please read this
Rob-350472
Rob-350472
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 684
Thanks for the indepth replies people - very useful - I tend to do quite a lot with dates so the more knowledge I have on the subject the better.
Bruce W Cassidy
Bruce W Cassidy
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 1033
I recommend using a Calendar table. You can encode the week start and week end in there. That's particularly good if your week starts on a "different from normal" day. Does your week start on a Sunday or a Monday?
ganesaselvam_thylak
ganesaselvam_thylak
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
hi Lynn,
Thanks for your query, can u explain the query?

select getdate(), dateadd(wk, datediff(wk, 0, getdate()) - 1, 0), dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 4
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search