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...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: