sqlserver得到指定日期中间的日期

chengrongman 2010-12-29 09:42:03

如开始日期是2010-01-12,结束日期是2010-01-15,要得到的日期是2010-01-13,2010-01-14

...全文

204 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

快溜 2010-12-29

打赏
举报



select convert(varchar(10),dateadd(dd,number+1,'2010-01-12'),120)

from master..spt_values

where type='p' and number < datediff(dd,'2010-01-12','2010-01-15')-1

dawugui 2010-12-29

打赏
举报

--sql 2005

declare @startDate datetime

declare @endDate datetime



SELECT @startDate = '2010-01-12' ,@endDate = '2010-01-15'

;WITH tb AS (

SELECT @startDate + 1 AS 'date'

UNION ALL

SELECT DATEADD(DAY,1,date) FROM tb WHERE DATE<@endDate - 1

)

SELECT tb.date from tb



/*

date

-----------------------

2010-01-13 00:00:00.000

2010-01-14 00:00:00.000



(2 行受影响)

*/

ohfox 2010-12-29

打赏
举报

后一天和前一天?
select dateadd(day,1,'你的日期'); 后一天
select dateadd(day,-1,'你的日期'); 前一天

dawugui 2010-12-29

打赏
举报

--sql 2000

declare @sdate datetime

declare @edate datetime

set @sdate = '2010-01-12'

set @edate = '2010-01-15'





select 

    dateadd(dd,num,@sdate) dt

from 

    (select isnull((select count(1) from sysobjects where id<t.id),0)+1 as num from sysobjects t) a

where

    dateadd(dd,num,@sdate)<@edate



/*

dt                                                     

------------------------------------------------------ 

2010-01-13 00:00:00.000

2010-01-14 00:00:00.000



（所影响的行数为 2 行）

*/

dawugui 2010-12-29

打赏
举报

--sql 2000

declare @sdate datetime

declare @edate datetime

set @sdate = '2010-01-12'

set @edate = '2010-01-15'





select 

    dateadd(dd,num,@sdate) dt

from 

    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a

where

    dateadd(dd,num,@sdate)<=@edate



/*

dt                                                     

------------------------------------------------------ 

2010-01-12 00:00:00.000

2010-01-13 00:00:00.000

2010-01-14 00:00:00.000

2010-01-15 00:00:00.000



（所影响的行数为 4 行）

*/