t-sql get all dates between 2 dates [duplicate]

by

Assuming SQL Server 2005+, use a recursive query:

WITH sample AS (
  SELECT CAST('2010-12-01' AS DATETIME) AS dt
  UNION ALL
  SELECT DATEADD(dd, 1, dt)
    FROM sample s
   WHERE DATEADD(dd, 1, dt) <= CAST('2010-12-04' AS DATETIME))
SELECT * 
  FROM sample

Returns:

 dt
 ---------
 2010-12-01 00:00:00.000
 2010-12-02 00:00:00.000
 2010-12-03 00:00:00.000
 2010-12-04 00:00:00.000

Use CAST/CONVERT to format as you like.

Using parameters for start & end:

INSERT INTO dbo.YOUR_TABLE
  (datetime_column)
WITH sample AS (
    SELECT @start_date AS dt
    UNION ALL
    SELECT DATEADD(dd, 1, dt)
      FROM sample s
     WHERE DATEADD(dd, 1, dt) <= @end_date)
SELECT s.dt
  FROM sample s

More Related Contents:

  1. SQl CASE Statement
  2. How to return only the Date from a SQL Server DateTime datatype
  3. Count work days between two dates
  4. How to calculate age in T-SQL with years, months, and days
  5. Convert multiple rows into one with comma as separator [duplicate]
  6. Get first day of week in SQL Server
  7. How to convert Seconds to HH:MM:SS using T-SQL
  8. T-SQL: Selecting rows to delete via joins
  9. Combining INSERT INTO and WITH/CTE
  10. What is a “batch”, and why is GO used?
  11. Getting the minimum of two values in SQL
  12. How do I exclude Weekend days in a SQL Server query?
  13. Get dates from a week number in T-SQL
  14. Date conversion and culture: Difference between DATE and DATETIME
  15. T-SQL loop over query results
  16. How to insert default values in SQL table?
  17. How to Round a Time in T-SQL
  18. T-SQL CASE Clause: How to specify WHEN NULL
  19. Check if a varchar is a number (TSQL)
  20. SQL “between” not inclusive
  21. Extract the first word of a string in a SQL Server query
  22. T-SQL – function with default parameters
  23. Search for one value in any column of any table inside a database
  24. How to use an Alias in a Calculation for Another Field
  25. Truncate Datetime to Second (Remove Milliseconds) in T-SQL
  26. Convert unix epoch timestamp to TSQL datetime
  27. T-SQL Between Dates Confusion
  28. Poor Man’s SQL Pivot. List Questions as Columns and Answers per User in one row
  29. Stored procedure hangs seemingly without explanation
  30. Date serial in SQL?

Leave a Comment