SQL 2005 First full week or first Monday of a year

Hello
How can i determine the first full week or the first monday of any given year as a varialbe or field in a SQL select statement
I would like to be able to use this value as a data point in some calculations.

Ex  2011 the first Monday is Jan3 and the Week no associated with that would be Week #2
with out hardcoding the date in the query I would like to have the query calculate this based on current date.
determine the first monday or first full week no for current year calculated from current date?
schuitkdsAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
You can find the first Monday for any year like so:

SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', CAST(YEAR(dateColumn) AS CHAR(4)) + '0107') / 7 * 7, '19000101')
0
 
LowfatspreadCommented:
you can do it as scott describes ... that does leave you open to potential confusion over the calendar system in operation (gregorian,julian,... ) if that may be important to you...

consider using a business calendar table to answer such questions...
0
 
Scott PletcherSenior DBACommented:
>> that does leave you open to potential confusion over the calendar system in operation (gregorian,julian,... ) <<

??? What ???


>> consider using a business calendar table to answer such questions... <<

That will be much more overhead, but of course you can do it that way.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.