额。我这样取的时间段。 create table #seTable(starTime nvarchar(40),endTime nvarchar(40)) --declare @seTable table(starTime nvarchar(40),endTime nvarchar(40)) declare @currentTime datetime declare @starTime datetime declare @endTime datetime declare @beginTime datetime set @currentTime = GETDATE() --当前时间 set @beginTime = CONVERT(varchar(10), getdate(), 120 ) --当天开始日期2014-02-11 00:00:00.000 set @starTime = DATEADD(hour,0,@currentTime)--2014-02-11 14:00:00.000 set @starTime = CONVERT(datetime, str(DATEPART(YEAR,@starTime))+'-'+str(DATEPART(MONTH,@starTime))+'-'+str(DATEPART(DAY,@starTime))+' '+str(DATEPART(HOUR,@starTime))+':00:00',120) set @endTime = DATEADD(hour,1,@currentTime) set @endTime = CONVERT(datetime, str(DATEPART(YEAR,@endTime))+'-'+str(DATEPART(MONTH,@endTime))+'-'+str(DATEPART(DAY,@endTime))+' '+str(DATEPART(HOUR,@endTime))+':00:00',120) insert into #seTable select top 24 convert(varchar(19),dateadd(hh,-langid,@starTime),120) starTime,convert(varchar(19),dateadd(hh,-langid,@endTime),120) endTime from master..syslanguages order by langid
你是想显示每个时间段的总和是吗,试试这个:;with t as ( select convert(varchar(10),getdate(),120) curr_date, dateadd(hour,s.number,convert(varchar(10),getdate(),120)) start_date, dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)) end_date from master..spt_values s where s.type = 'P' and s.number <= 23 ) select t.start_date,t.end_date,sum(F_HourEquValue) from t left join 数据表 on f_meterCode =10488 and f_startHour >=start_date and F_endHour <end_date
修改一下: --产生当天的时间段,开始时间,结束时间 ;with t as ( select convert(varchar(10),getdate(),120) curr_date, dateadd(hour,s.number,convert(varchar(10),getdate(),120)) start_date, dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)) end_date from master..spt_values s where s.type = 'P' and s.number <= 23 ) select t.start_date,t.end_date,sum(F_HourEquValue) from t left join 数据表 on f_meterCode =10488 and f_startHour >=start_date and F_endHour <end_date group by t.start_date,t.end_date
哦,这个可以适当简化:if OBJECT_ID('tempdb..#seTable') is not null drop table #seTable
--产生当天的时间段,开始时间,结束时间 ;with t as ( select convert(varchar(10),getdate(),120) curr_date, dateadd(hour,s.number,convert(varchar(10),getdate(),120)) start_date, dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)) end_date from master..spt_values s where s.type = 'P' and s.number <= 23 )
select t.start_date,t.end_date,sum(F_HourEquValue) from t left join 数据表 on f_meterCode =10488 and f_startHour >=start_date and F_endHour <end_date and F_endHour <='2012-09-08 00:00:00.000' and f_startHour>='2014-09-08 01:00:00.000' group by t.start_date,t.end_date
加上时间条件,是这样吗: ;with t as ( select convert(varchar(10),getdate(),120) curr_date, dateadd(hour,s.number,convert(varchar(10),getdate(),120)) start_date, dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)) end_date from master..spt_values s where s.type = 'P' and s.number <= 23 ) select t.start_date,t.end_date,sum(F_HourEquValue) from t left join 数据表 on f_meterCode =10488 and f_startHour >=start_date and F_endHour <end_date where t.start_date = '2012-09-08 00:00:00.000' and t.end_date ='2012-09-08 01:00:00.000'group by t.start_date,t.end_date
不是,因为我的数据是2012-09-08的 所以这个时间应该取 2012-09-08 0:00 - 2012-09-09 0:00这些我用的老的取时间方法,但是只取出了12点到13点的数据。见16楼;with t as ( select convert(varchar(10),getdate(),120) curr_date, dateadd(hour,s.number,convert(varchar(10),getdate(),120)) start_date, dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)) end_date from master..spt_values s where s.type = 'P' and s.number <= 23 )加上时间条件,是这样吗: ;with t as ( select convert(varchar(10),getdate(),120) curr_date, dateadd(hour,s.number,convert(varchar(10),getdate(),120)) start_date, dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)) end_date from master..spt_values s where s.type = 'P' and s.number <= 23 ) select t.start_date,t.end_date,sum(F_HourEquValue) from t left join 数据表 on f_meterCode =10488 and f_startHour >=start_date and F_endHour <end_date where t.start_date = '2012-09-08 00:00:00.000' and t.end_date ='2012-09-08 01:00:00.000'group by t.start_date,t.end_date
where f_meterCode =10488
select convert(varchar(10),getdate(),120) '当天日期',
convert(varchar(5),dateadd(hour,s.number,convert(varchar(10),getdate(),120)),108)+'-'+
convert(varchar(5),dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)),108) '时间段'
from master..spt_values s
where s.type = 'P' and s.number <= 23
/*
当天日期 时间段
2014-02-11 00:00-01:00
2014-02-11 01:00-02:00
2014-02-11 02:00-03:00
2014-02-11 03:00-04:00
2014-02-11 04:00-05:00
2014-02-11 05:00-06:00
2014-02-11 06:00-07:00
2014-02-11 07:00-08:00
2014-02-11 08:00-09:00
2014-02-11 09:00-10:00
2014-02-11 10:00-11:00
2014-02-11 11:00-12:00
2014-02-11 12:00-13:00
2014-02-11 13:00-14:00
2014-02-11 14:00-15:00
2014-02-11 15:00-16:00
2014-02-11 16:00-17:00
2014-02-11 17:00-18:00
2014-02-11 18:00-19:00
2014-02-11 19:00-20:00
2014-02-11 20:00-21:00
2014-02-11 21:00-22:00
2014-02-11 22:00-23:00
2014-02-11 23:00-00:00
*/
额。我这样取的时间段。 create table #seTable(starTime nvarchar(40),endTime nvarchar(40))
--declare @seTable table(starTime nvarchar(40),endTime nvarchar(40))
declare @currentTime datetime
declare @starTime datetime
declare @endTime datetime
declare @beginTime datetime
set @currentTime = GETDATE() --当前时间
set @beginTime = CONVERT(varchar(10), getdate(), 120 ) --当天开始日期2014-02-11 00:00:00.000
set @starTime = DATEADD(hour,0,@currentTime)--2014-02-11 14:00:00.000
set @starTime = CONVERT(datetime, str(DATEPART(YEAR,@starTime))+'-'+str(DATEPART(MONTH,@starTime))+'-'+str(DATEPART(DAY,@starTime))+' '+str(DATEPART(HOUR,@starTime))+':00:00',120)
set @endTime = DATEADD(hour,1,@currentTime)
set @endTime = CONVERT(datetime, str(DATEPART(YEAR,@endTime))+'-'+str(DATEPART(MONTH,@endTime))+'-'+str(DATEPART(DAY,@endTime))+' '+str(DATEPART(HOUR,@endTime))+':00:00',120)
insert into #seTable select top 24 convert(varchar(19),dateadd(hh,-langid,@starTime),120) starTime,convert(varchar(19),dateadd(hh,-langid,@endTime),120) endTime from master..syslanguages order by langid
as
(
select convert(varchar(10),getdate(),120) curr_date,
dateadd(hour,s.number,convert(varchar(10),getdate(),120)) start_date,
dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)) end_date
from master..spt_values s
where s.type = 'P' and s.number <= 23
)
select t.start_date,t.end_date,sum(F_HourEquValue)
from t
left join 数据表
on f_meterCode =10488
and f_startHour >=start_date
and F_endHour <end_date
--产生当天的时间段,开始时间,结束时间
;with t
as
(
select convert(varchar(10),getdate(),120) curr_date,
dateadd(hour,s.number,convert(varchar(10),getdate(),120)) start_date,
dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)) end_date
from master..spt_values s
where s.type = 'P' and s.number <= 23
)
select t.start_date,t.end_date,sum(F_HourEquValue)
from t
left join 数据表
on f_meterCode =10488
and f_startHour >=start_date
and F_endHour <end_date
group by t.start_date,t.end_date
哦,这个可以适当简化:if OBJECT_ID('tempdb..#seTable') is not null
drop table #seTable
create table #seTable(starTime nvarchar(40),endTime nvarchar(40))
insert into #seTable
select --convert(varchar(10),getdate(),120) curr_date,
convert(nvarchar(19),dateadd(hour,s.number,convert(varchar(10),getdate(),120)),120) start_date,
convert(nvarchar(19),dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)),120) end_date
from master..spt_values s
where s.type = 'P' and s.number <= 23select *
from #seTable
/*
starTime endTime
2014-02-11 00:00:00 2014-02-11 01:00:00
2014-02-11 01:00:00 2014-02-11 02:00:00
2014-02-11 02:00:00 2014-02-11 03:00:00
2014-02-11 03:00:00 2014-02-11 04:00:00
2014-02-11 04:00:00 2014-02-11 05:00:00
2014-02-11 05:00:00 2014-02-11 06:00:00
2014-02-11 06:00:00 2014-02-11 07:00:00
2014-02-11 07:00:00 2014-02-11 08:00:00
2014-02-11 08:00:00 2014-02-11 09:00:00
2014-02-11 09:00:00 2014-02-11 10:00:00
2014-02-11 10:00:00 2014-02-11 11:00:00
2014-02-11 11:00:00 2014-02-11 12:00:00
2014-02-11 12:00:00 2014-02-11 13:00:00
2014-02-11 13:00:00 2014-02-11 14:00:00
2014-02-11 14:00:00 2014-02-11 15:00:00
2014-02-11 15:00:00 2014-02-11 16:00:00
2014-02-11 16:00:00 2014-02-11 17:00:00
2014-02-11 17:00:00 2014-02-11 18:00:00
2014-02-11 18:00:00 2014-02-11 19:00:00
2014-02-11 19:00:00 2014-02-11 20:00:00
2014-02-11 20:00:00 2014-02-11 21:00:00
2014-02-11 21:00:00 2014-02-11 22:00:00
2014-02-11 22:00:00 2014-02-11 23:00:00
2014-02-11 23:00:00 2014-02-12 00:00:00
*/
;with t
as
(
select convert(varchar(10),getdate(),120) curr_date,
dateadd(hour,s.number,convert(varchar(10),getdate(),120)) start_date,
dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)) end_date
from master..spt_values s
where s.type = 'P' and s.number <= 23
)
select t.start_date,t.end_date,sum(F_HourEquValue)
from t
left join 数据表
on f_meterCode =10488
and f_startHour >=start_date
and F_endHour <end_date
and F_endHour <='2012-09-08 00:00:00.000' and f_startHour>='2014-09-08 01:00:00.000'
group by t.start_date,t.end_date
可是我查询制查找到了12点到13点的数据
加上时间条件,是这样吗:
;with t
as
(
select convert(varchar(10),getdate(),120) curr_date,
dateadd(hour,s.number,convert(varchar(10),getdate(),120)) start_date,
dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)) end_date
from master..spt_values s
where s.type = 'P' and s.number <= 23
)
select t.start_date,t.end_date,sum(F_HourEquValue)
from t
left join 数据表
on f_meterCode =10488
and f_startHour >=start_date
and F_endHour <end_date
where t.start_date = '2012-09-08 00:00:00.000'
and t.end_date ='2012-09-08 01:00:00.000'group by t.start_date,t.end_date
所以这个时间应该取 2012-09-08 0:00 - 2012-09-09 0:00这些我用的老的取时间方法,但是只取出了12点到13点的数据。见16楼;with t
as
(
select convert(varchar(10),getdate(),120) curr_date,
dateadd(hour,s.number,convert(varchar(10),getdate(),120)) start_date,
dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)) end_date
from master..spt_values s
where s.type = 'P' and s.number <= 23
)加上时间条件,是这样吗:
;with t
as
(
select convert(varchar(10),getdate(),120) curr_date,
dateadd(hour,s.number,convert(varchar(10),getdate(),120)) start_date,
dateadd(hour,s.number+1,convert(varchar(10),getdate(),120)) end_date
from master..spt_values s
where s.type = 'P' and s.number <= 23
)
select t.start_date,t.end_date,sum(F_HourEquValue)
from t
left join 数据表
on f_meterCode =10488
and f_startHour >=start_date
and F_endHour <end_date
where t.start_date = '2012-09-08 00:00:00.000'
and t.end_date ='2012-09-08 01:00:00.000'group by t.start_date,t.end_date
FROM (SELECT CONVERT(VARCHAR, Dateadd(hour, number, CONVERT(SMALLDATETIME, CONVERT(DATE, Getdate()))), 120) AS mytime
FROM master.dbo.spt_values
WHERE type = 'P')t
WHERE mytime <= Getdate();
--return
/*
2014-02-12 00:00:00~2014-02-12 00:59:59
2014-02-12 01:00:00~2014-02-12 01:59:59
2014-02-12 02:00:00~2014-02-12 02:59:59
2014-02-12 03:00:00~2014-02-12 03:59:59
2014-02-12 04:00:00~2014-02-12 04:59:59
2014-02-12 05:00:00~2014-02-12 05:59:59
2014-02-12 06:00:00~2014-02-12 06:59:59
2014-02-12 07:00:00~2014-02-12 07:59:59
2014-02-12 08:00:00~2014-02-12 08:59:59
2014-02-12 09:00:00~2014-02-12 09:59:59
2014-02-12 10:00:00~2014-02-12 10:59:59
2014-02-12 11:00:00~2014-02-12 11:59:59
2014-02-12 12:00:00~2014-02-12 12:59:59
2014-02-12 13:00:00~2014-02-12 13:59:59
2014-02-12 14:00:00~2014-02-12 14:59:59
2014-02-12 15:00:00~2014-02-12 15:59:59
2014-02-12 16:00:00~2014-02-12 16:59:59
*/