June 18, 2013 at 9:09 am
Hi,
Newbie here would like some help if possible. I’m trying to write a sql statement to extract “Previous” and “Current Week” from attendance table. The field for date is EnrollmentDate([highlight=#ffff11]datetimeoffset[/highlight](7), null) also our weeks are from Sunday through Saturday. I tried writing the statement belown but can’t get the correct Sunday through Saturday week for both “Previous” and “Current Week”. I believe it's because field is datetimeoffset??? Any and all help is most definitely appreciated.
“Current Week”
“I GET June 17 – 22 but not Sunday June 16”
Select * from dbo.Enrollment
Where enrollmentdate between DATEADD(wk,DATEDIFF(wk,0,GETDATE()), 0) and DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 6)
“Previous Week”
“I GET June 9 – 14 but not Saturday June 15”
Select * from dbo.Enrollment
Where enrollmentdate BETWEEN DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) AND DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6)
June 18, 2013 at 11:51 am
See if this article helps point you in the right direction.
http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2013 at 4:07 pm
Your problem is that you are adding the number of weeks to the base date of zero (1 January 1900), which happened to be a Monday. Changing your queries to something like
DATEADD ( wk, DATEDIFF ( wk, 0, GETDATE() ), '1899-12-31' )
will give you the correct result if you want the week to start on Sunday.
Regards,
Jan
June 19, 2013 at 11:32 am
Thank you for the help.
June 19, 2013 at 1:31 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply