How to calculate the number of “Tuesdays” between two dates in TSQL?

by

Thank you t-clausen.dk, Saved me few days.
To get no of instances of each day:

declare @from datetime="3/1/2013" 
declare @to datetime="3/31/2013" 


select 
 datediff(day, -7, @to)/7-datediff(day, -6, @from)/7 AS MON,
 datediff(day, -6, @to)/7-datediff(day, -5, @from)/7 AS TUE,
 datediff(day, -5, @to)/7-datediff(day, -4, @from)/7 AS WED,
 datediff(day, -4, @to)/7-datediff(day, -3, @from)/7 AS THU,
 datediff(day, -3, @to)/7-datediff(day, -2, @from)/7 AS FRI,
 datediff(day, -2, @to)/7-datediff(day, -1, @from)/7 AS SAT,
 datediff(day, -1, @to)/7-datediff(day, 0, @from)/7 AS SUN

More Related Contents:

  1. How to return only the Date from a SQL Server DateTime datatype
  2. Best approach to remove time part of datetime in SQL Server
  3. Count work days between two dates
  4. SQL “between” not inclusive
  5. Date serial in SQL?
  6. Calculate a Running Total in SQL Server
  7. Not equal != operator on NULL
  8. SET NOCOUNT ON usage
  9. How can I get column names from a table in SQL Server?
  10. How can I select the first day of a month in SQL?
  11. Select statement to find duplicates on certain fields
  12. Getting only Month and Year from SQL DATE
  13. Getting list of tables, and fields in each, in a database
  14. WHERE Clause vs ON when using JOIN
  15. Is it possible to select sql server data using column ordinal position
  16. @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity
  17. How do you do date math that ignores the year?
  18. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  19. How to create a PivotTable in Transact/SQL?
  20. Dynamic SQL to generate column names?
  21. How to split string using delimiter char using T-SQL?
  22. I want to use CASE statement to update some records in sql server 2005
  23. SQL server select distinct rows using most recent value only
  24. Generate all combinations in SQL
  25. How to return a incremental group number per group in SQL
  26. How to search SQL column containing JSON array
  27. Eliminate and reduce overlapping date ranges
  28. What does collation mean?
  29. COALESCE Function in TSQL
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment