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+，使用递归查询:

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

返回:

 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 以随意格式化.>

使用参数启动&结束:

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屋！