SQL 2005 First full week or first Monday of a year

Posted on 2011-10-26
Medium Priority
Last Modified: 2012-06-27
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?
Question by:schuitkds
  • 2
LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 37033680
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')
LVL 50

Expert Comment

ID: 37049485
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...
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37053880
>> 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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Screencast - Getting to Know the Pipeline

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question