Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Previous week date range Expand / Collapse
Author
Message
Posted Wednesday, March 11, 2009 9:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
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.
Post #673397
Posted Wednesday, March 11, 2009 10:07 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:10 AM
Points: 3,105, Visits: 11,498
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')


Post #673427
Posted Wednesday, March 11, 2009 10:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
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!
Post #673438
Posted Wednesday, March 11, 2009 11:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 9:37 AM
Points: 2,651, Visits: 5,983
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
Post #673567
Posted Wednesday, March 11, 2009 11:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:22 PM
Points: 20,680, Visits: 32,279
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




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)
Post #673575
Posted Wednesday, March 11, 2009 12:17 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:10 AM
Points: 3,105, Visits: 11,498
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


Post #673603
Posted Wednesday, March 11, 2009 12:51 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 9:37 AM
Points: 2,651, Visits: 5,983
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
Post #673630
Posted Thursday, March 12, 2009 3:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
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.
Post #674016
Posted Thursday, March 12, 2009 1:21 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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?
Post #674640
Posted Tuesday, September 1, 2009 12:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 19, 2009 1:00 AM
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
Post #780467
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse