由日期期间返回具体的日期

2008-11-17 05:31:10 195 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

sunylf 墨衍会员 · AI 创作全网分发关注 + 加入社区

SAP函数计算两个日期期间的天数函数：FIMA_DAYS_AND_MONTHS_AND_YEARS 阅读详情

有个表
BeginDate EndDate
2009-1-25 2009-1-28
如何获得
2009-1-25
2009-1-26
2009-1-27
2009-1-28的记录啊

最好能用一条语句实现,sqlsever 2000.

...全文

Word中 Go to endnote 出现闪退问题解决办法 Word中 Go to endnote 出现闪退问题解决办法阅读详情

sunylf

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

ws_hgo 2008-11-17

打赏
举报

if object_id('FFFF') is not null drop table FFFF

create table FFFF

(

   tdate datetime

)

declare @i int

set @i=(select datediff(dd,'2009-1-25','2009-1-28'))

select dateadd(dd,1,'2009-1-25')

while @i>0

begin

  

  insert into FFFF select dateadd(dd,@i-1,'2009-1-25')

  set @i=@i-1

  if(@i=1)

  begin

  insert into FFFF select '2009-1-28'

  end

end

select * from FFFF

ws_hgo 2008-11-17

打赏
举报

create table FFFF

(

   tdate datetime

)

declare @i int

set @i=(select datediff(dd,'2009-1-25','2009-1-28'))

select dateadd(dd,1,'2009-1-25')

while @i>0

begin

  insert into FFFF select dateadd(dd,@i-1,'2009-1-25')

  set @i=@i-1

end

select * from FFFF

csdyyr 2008-11-17

打赏
举报

又学了一招

fcuandy 2008-11-17

打赏
举报

不过说实话，楼主问时，我第一个想到的是select identity ... into ..然后dateadd,或者是union再join,
压根没想到count计数..脑子还是不够快。

钻钻要顶。

butchroller 2008-11-17

打赏
举报

select rn = identity(int, 0, 1) into #t from sysobjects;

select m.BeginDate + t.rn from #t t, mytab m where m.BeginDate + t.rn <= m.EndDate;

drop table #t;

-狙击手- 2008-11-17

打赏
举报

[Quote=引用 11 楼 fcuandy 的回复:]
而且跨度很大的话，连两三次系统表记录也未必够，连多了根本不敢跑..
[/Quote]

呵呵，那当然　

fcuandy 2008-11-17

打赏
举报

楼主最初问过，问的是

。。。
2007-1-1
2007-1-2
。。。
2007-12-31
2008-1-1
。。。
2008-12-31
2009-1-1
。。。
2009-12-31

群里那个是你吧

-狙击手- 2008-11-17

打赏
举报

select a.n+10*b.n as n

from (select 0 as n union all select 1  union all select 2  

union all select 3  union all select 4  union all select 5  

union all select 6  union all select 7  union all select 8  union all select 9 ) a

,(select 0 as n union all select 1  union all select 2  

union all select 3  union all select 4  union all select 5  

union all select 6  union all select 7  union all select 8  union all select 9 ) b

fcuandy 2008-11-17

打赏
举报

而且跨度很大的话，连两三次系统表记录也未必够，连多了根本不敢跑..

dobear_0922 2008-11-17

打赏
举报

[Quote=引用 7 楼 libin_ftsafe 的回复:]

declare @t table(BeginDate datetime,EndDate datetime)

insert into @t select '2009-1-25','2009-1-28'



select

    dateadd(dd,b.id,a.BeginDate) as date

from

    @t a,

    (select (select count(1) from sysobjects where id<t.id) as id from sysobjects t) b

where

    b.id<=datediff(dd,BeginDate,EndDate)



/*

date                                                   

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

2009-01-25 00:00:00.000

2009-01-26 00:00:00.000

2009-01-27 00:00:00.000

2009-01-28 00:00:00.000

*/

[/Quote]
顶钻钻，，，

fcuandy 2008-11-17

打赏
举报

union再cross join,或连几个系统表，我宁原写循环,效率好多了。

-狙击手- 2008-11-17

打赏
举报

[Quote=引用 4 楼 fcuandy 的回复:]
一条语句不可能。

时间间隔大了，你写union啊。
[/Quote]
那可以两个union 到10的子集　进行乘运算

子陌红尘 2008-11-17

打赏
举报



declare @t table(BeginDate datetime,EndDate datetime)

insert into @t select '2009-1-25','2009-1-28'



select

    dateadd(dd,b.id,a.BeginDate) as date

from

    @t a,

    (select (select count(1) from sysobjects where id<t.id) as id from sysobjects t) b

where

    b.id<=datediff(dd,BeginDate,EndDate)



/*

date                                                   

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

2009-01-25 00:00:00.000

2009-01-26 00:00:00.000

2009-01-27 00:00:00.000

2009-01-28 00:00:00.000

*/

ws_hgo 2008-11-17

打赏
举报

等下

-狙击手- 2008-11-17

打赏
举报

select 0 as n union all select 1 union select 2 union all select 3 union all select 4

－－
如果时间跨度大，就把这个接长点

fcuandy 2008-11-17

打赏
举报

一条语句不可能。

时间间隔大了，你写union啊。

ws_hgo 2008-11-17

打赏
举报

ws_hgo 2008-11-17

打赏
举报

-狙击手- 2008-11-17

打赏
举报

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

-- Author:  happyflsytone  

-- Date:2008-11-17 17:32:45

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



-- Test Data: TA

IF OBJECT_ID('TA') IS NOT NULL 

    DROP TABLE TA

Go

CREATE TABLE TA(BeginDate SMALLDATETIME,EndDate SMALLDATETIME)

Go

INSERT INTO TA

 SELECT '2009-1-25','2009-1-28' 

GO

--Start

SELECT 

	dateadd(d,n, BeginDate) as d

FROM 

	TA a,

(select 0 as n union all select 1 union select 2 union all select 3 union all select 4) b

where dateadd(d,n, BeginDate) <= EndDate



--Result:

/*



d

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

2009-01-25 00:00:00

2009-01-26 00:00:00

2009-01-27 00:00:00

2009-01-28 00:00:00



(4 行受影响)

*/

--End

由日期期间返回具体的日期

相关推荐