sql server取某个时间段内所有日期或者所有月份
取所有月份:
declare
@
begin
datetime,@
end
datetime
set
@
begin
=
'2015-2-6'
set
@
end
=
'2015-12-2'
declare
@months
int
set
@months=DATEDIFF(
month
,@
begin
,@
end
)
select
convert
(
varchar
(7),DATEADD(
month
,number,@
begin
) ,120)
AS
月份
from
master.dbo.spt_values
where
type=
'P'
AND
number<=@months
取所有日期:
DECLARE
@days
INT
,
@date_start DATETIME =
'2017-03-15'
,
@date_end DATETIME =
'2017-04-13'
SET
@days = DATEDIFF(
DAY
, @DATE_START, @DATE_END);
SELECT
convert
(
char
(10), DATEADD(dd, number, @DATE_START),120)
AS
yyyymmdd
FROM
master.dbo.spt_values
as
spt
WHERE
type =
'P'
AND
number <= @days
注意:'P'需要大写