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

Newbie needs help Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 9:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 1:33 PM
Points: 2, Visits: 10
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)

Post #1464721
Posted Tuesday, June 18, 2013 11:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 13,085, Visits: 12,549
See if this article helps point you in the right direction.

http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1464816
Posted Tuesday, June 18, 2013 4:07 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
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


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1464898
Posted Wednesday, June 19, 2013 11:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 1:33 PM
Points: 2, Visits: 10
Thank you for the help.
Post #1465305
Posted Wednesday, June 19, 2013 1:31 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
No problem, that's what we are here for.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1465354
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse