Get dates from a week number in T-SQL

by

Quassnoi’s answer works, but kind of leaves you on the hook for cleaning up the dates if they are dates in the middle of the day (his start of week leaves you one day earlier than you need to be if you use a time in the middle of the day — you can test using GETDATE()).

I’ve used something like this in the past:

SELECT 
   CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, DATECOL), DATECOL)), 101),
   CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, DATECOL) - 6, DATECOL)), 101)

A side benefit of this is that by using @@DATEFIRST you can handle nonstandard week starting days (the default is Sunday, but with SET @@DATEFIRST you can change this).

It seems crazy that simple date manipulation in SQL Server has to be this arcane, but there you go…

More Related Contents:

  1. Get first day of week in SQL Server
  2. How do I exclude Weekend days in a SQL Server query?
  3. Date conversion and culture: Difference between DATE and DATETIME
  4. How do I create a unique constraint that also allows nulls?
  5. How do I get list of all tables in a database using TSQL?
  6. What is the significance of 1/1/1753 in SQL Server?
  7. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  8. Escape a string in SQL Server so that it is safe to use in LIKE expression
  9. Insert Update trigger how to determine if insert or update
  10. SQL time difference between two dates result in hh:mm:ss
  11. What is the best way to truncate a date in SQL Server?
  12. Dynamic SQL (passing table name as parameter)
  13. SQL Server: Difference between PARTITION BY and GROUP BY
  14. How do you truncate all tables in a database using TSQL?
  15. Understanding SQL Server LOCKS on SELECT queries
  16. TSQL Email Validation (without regex)
  17. Dynamic SQL results into temp table in SQL Stored procedure
  18. SQL Server Output Clause into a scalar variable
  19. Sequence as default value for a column
  20. TABLOCK vs TABLOCKX
  21. How to convert number of minutes to hh:mm format in TSQL?
  22. How do I select last 5 rows in a table without sorting?
  23. SELECT INTO a table variable in T-SQL
  24. T-SQL query to show table definition?
  25. How can I insert binary file data into a binary SQL field using a simple insert statement?
  26. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  27. Include missing months in Group By query
  28. Is there an “IF EXISTS” test for a linked server?
  29. Date serial in SQL?
  30. SQL Server stored procedure return code oddity

Leave a Comment