How to get week number of the month from the date in sql server 2008

by

Here are 2 different ways, both are assuming the week starts on monday

If you want weeks to be whole, so they belong to the month in which they start:
So saturday 2012-09-01 and sunday 2012-09-02 is week 4 and monday 2012-09-03 is week 1 use this:

DECLARE @date date="2012-09-01"
SELECT (day(datediff(d,0,@date)/7*7)-1)/7+1

If your weeks cut on monthchange so saturday 2012-09-01 and sunday 2012-09-02 is week 1 and monday 2012-09-03 is week 2 use this:

DECLARE @date date="2012-09-01"
SELECT 
  datediff(ww,datediff(d,0,dateadd(m,datediff(m,7,@date),0)
    )/7*7,dateadd(d,-1,@date))+1

I received an email from Gerald. He pointed out a flaw in the second method. This should be fixed now

I received an email from Ben Wilkins. He pointed out a flaw in the first method. This should be fixed now

More Related Contents:

  1. T-SQL: Export to new Excel file
  2. SQL MERGE statement to update data
  3. How to get the last record per group in SQL
  4. T-SQL substring – separating first and last name
  5. T-SQL strip all non-alpha and non-numeric characters
  6. Avoiding IF ELSE due to variable been NULL
  7. Recursive Child/Parent queries in T/SQL
  8. T-SQL split string
  9. How can I get column names from a table in SQL Server?
  10. Conditional WHERE clause in SQL Server
  11. How can I select the first day of a month in SQL?
  12. Remove trailing zeros from decimal in SQL Server
  13. Select statement to find duplicates on certain fields
  14. Enable ‘xp_cmdshell’ SQL Server
  15. How to create a PivotTable in Transact/SQL?
  16. How does sql server sort your data?
  17. T-SQL and the WHERE LIKE %Parameter% clause
  18. Which SQL query is faster? Filter on Join criteria or Where clause?
  19. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  20. Generate all combinations in SQL
  21. Optional parameters in SQL Server stored procedure
  22. Entity Framework Database.SetInitializer simply not working
  23. How can I group by date time column without taking time into consideration
  24. Cannot open backup device. Operating System error 5
  25. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  26. SQL Server SELECT INTO @variable?
  27. Can I pass variable to select statement as column name in SQL Server [duplicate]
  28. SQL Server 2008 Connection Error “No process is on the other end of the pipe”
  29. Using CASE Statement inside IN Clause
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment