22,209
社区成员
发帖
与我相关
我的任务
分享
select p.date,k.TestforData
from
(select * from dbo.F_DATELIST ('2009-08-06','2009-09-04')) as p
left join TestData as k
on datediff(day,p.date,k.TestTime)=0
--功能:返回两个日期间的时间列表
IF EXISTS (SELECT 1 FROM [dbo].SYSOBJECTS WHERE NAME = 'F_DATELIST ')
DROP FUNCTION F_DATELIST
GO
CREATE FUNCTION F_DATELIST
(
@BEGIN_DATE AS DATETIME,
@END_TIME AS DATETIME
) RETURNS @TEMPTABLE TABLE (DATE DATETIME )
AS
BEGIN
WHILE(datediff(day,@BEGIN_DATE , @END_TIME)>=0)
BEGIN
INSERT INTO @TEMPTABLE(DATE)
VALUES(@BEGIN_DATE)
SET @BEGIN_DATE=dateadd(day,1,@BEGIN_DATE)
END
RETURN
END
GO
select p.date,k.TestforData
from
(select * from dbo.F_DATELIST ('2009-08-06','2009-09-04')) as p
join TestData as k
on datediff(day,p.date,k.TestTime)=0
--功能:返回两个日期间的时间列表
IF EXISTS (SELECT 1 FROM [dbo].SYSOBJECTS WHERE NAME = 'F_DATELIST ')
DROP FUNCTION F_DATELIST
GO
CREATE FUNCTION F_DATELIST
(
@BEGIN_DATE AS DATETIME,
@END_TIME AS DATETIME
) RETURNS @TEMPTABLE TABLE (DATE DATETIME )
AS
BEGIN
WHILE(datediff(day,@BEGIN_DATE , @END_TIME)>=0)
BEGIN
INSERT INTO @TEMPTABLE(DATE)
VALUES(@BEGIN_DATE)
SET @BEGIN_DATE=dateadd(day,1,@BEGIN_DATE)
END
RETURN
END
GO
select p.date,k.TestforData
from
(select * from dbo.F_DATELIST ('2009-08-06','2009-09-04')) as p
join TestData as k
declare @bt datetime,@et datetime,@n int
set @n=DATEDIFF(DAY,@bt,@et)
select k.rq,p.col
from (select DATEADD(DAY,number,dateadd(day,-1,@n)) as rq
from master..spt_values where [type] = 'P' and number between 1 and @n ) k
left join TestData p on datediff(day,k.rq,p.日期字段)=0
--功能:返回两个日期间的时间列表
IF EXISTS (SELECT 1 FROM [dbo].SYSOBJECTS WHERE NAME = 'F_DATELIST ')
DROP FUNCTION F_DATELIST
GO
CREATE FUNCTION F_DATELIST
(
@BEGIN_DATE AS DATETIME,
@END_TIME AS DATETIME
) RETURNS @TEMPTABLE TABLE (DATE DATETIME )
AS
BEGIN
WHILE(datediff(day,@BEGIN_DATE , @END_TIME)>=0)
BEGIN
INSERT INTO @TEMPTABLE(DATE)
VALUES(@BEGIN_DATE)
SET @BEGIN_DATE=dateadd(day,1,@BEGIN_DATE)
END
RETURN
END
GO
select p.date,k.col2
from (select * from dbo.F_DATELIST ('2009-08-06','2009-09-04' ) as p join TestData as k
on datediff(day,p.date,k.日期字段)=0