可能的重复:
获取日期范围内的日期 https://stackoverflow.com/questions/271595/getting-dates-between-a-range-of-dates
假设我有 2 个日期(仅日期部分,没有时间),我想获取这 2 个日期之间的所有日期(包括这两个日期)并将它们插入到表中。有没有一种简单的方法可以使用 SQL 语句(即无需循环)来完成此操作?
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
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 为您喜欢的格式 http://msdn.microsoft.com/en-us/library/ms187928.aspx.
使用开始和结束参数:
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)