SQL Clone
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
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 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
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5692 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
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 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
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3936 Visits: 6127
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-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39039 Visits: 38514
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
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5692 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
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3936 Visits: 6127
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
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1271 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
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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