t-sql获取2个日期之间的所有日期 [英] t-sql get all dates between 2 dates

问题描述

可能重复：

在日期范围之间获取日期

假设我有2个日期（仅限日期部分，没有时间），我想在这两个日期之间取得所有日期，并将其插入表格。有没有一个简单的方法来做一个SQL语句（即没有循环）？

Let's say I have 2 dates (date part only, no time) and I want to get all dates between these 2 dates inclusive and insert them in a table. Is there an easy way to do it with a SQL statement (i.e without looping)?

Ex:
Date1: 2010-12-01
Date2: 2010-12-04

Table should have following dates:
2010-12-01, 2010-12-02, 2010-12-03, 2010-12-04

推荐答案

假设SQL Server 2005 +，使用递归查询：

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

使用 CAST / CONVERT to for垫，你喜欢。

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

这篇关于t-sql获取2个日期之间的所有日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！