日期:2014-05-18  浏览次数:20423 次

如何求出给定时间当月每一天的日期?
例如给定一个时间为 “2012-05-08”,
希望得出当月每一天的日期,即结果为
2012-05-01
2012-05-02
2012-05-03
2012-05-04
2012-05-05
…………
2012-05-31

用SQL语句该如何写呢?

------解决方案--------------------
SQL code

DECLARE @DATE VARCHAR(10)
SET @DATE = '2012-05-08'

SELECT CONVERT(VARCHAR(10),DATEADD(DAY,number,LEFT(@DATE,8) + '01'),120) AS Date
FROM master..spt_values
WHERE type = 'P' AND (number BETWEEN 0 AND 32) AND DATEADD(DAY,number,LEFT(@DATE,8) + '01') < DATEADD(MONTH,1,LEFT(@DATE,8) + '01')

Date
2012-05-01
2012-05-02
2012-05-03
2012-05-04
2012-05-05
2012-05-06
2012-05-07
2012-05-08
2012-05-09
2012-05-10
2012-05-11
2012-05-12
2012-05-13
2012-05-14
2012-05-15
2012-05-16
2012-05-17
2012-05-18
2012-05-19
2012-05-20
2012-05-21
2012-05-22
2012-05-23
2012-05-24
2012-05-25
2012-05-26
2012-05-27
2012-05-28
2012-05-29
2012-05-30
2012-05-31