时间精确和转换的问题..
declare @startTime datetime
declare @endTime datetime
declare @s_1 char(10)
declare @s_2 char(10)
set @s_1 = '00:00:00 '
set @s_2 = '59:59:00 '
set @startTime= '2004-05-26 '
set @endTime = '9999-12-31 '
select surveypointno,avg(middlevalue1) as minddlevalue1,avg(finalvalue1) as finalvalue1 into #a from a11czzk1
where surveytime between @starttime and @endtime
group by surveypointno
select surveypointno,avg(middlevalue1) as minddlevalue1,avg(finalvalue1) as finalvalue1 into #b from a11czzk1
where surveytime between @starttime and dateadd(day,10,@starttime)
group by surveypointno
select surveypointno,avg(middlevalue1) as minddlevalue1,avg(finalvalue1) as finalvalue1 into #c from a11czzk1
where surveytime > dateadd(day,10,@starttime) and surveytime < = dateadd(day,20,@starttime)
group by surveypointno
select surveypointno,avg(middlevalue1) as minddlevalue1,avg(finalvalue1) as finalvalue1 into #d from a11czzk1
where surveytime > dateadd(day,20,@starttime) and surveytime <= @endtime,20)
group by surveypointno
我想在where里面把@starttime和@endtime精确到秒,而不是在set里面,而且不能漏数据.应该怎么整各位大侠拜托了.谢谢啊```在线跪等中...
[解决办法]
是不是這個意思？
select surveypointno,avg(middlevalue1) as minddlevalue1,avg(finalvalue1) as finalvalue1 into #a from a11czzk1
where surveytime between @starttime + ' ' + @s_1 and @endtime + ' ' + @s_2
group by surveypointno
[解决办法]
declare @startTime datetime
declare @endTime datetime
declare @s_1 char(10)
declare @s_2 char(10)
set @s_1 = '00:00:00 '
set @s_2 = '23:59:59 ' --這裡需要修改
set @startTime= '2004-05-26 '
set @endTime = '9999-12-31 '
where surveytime between @starttime and @endtime
改成
where surveytime between @starttime + ' ' + @s_1 and @endtime + ' ' + @s_2
這個就可以了
查詢的區間就變為了 "2004-05-26 00:00:00.000 " And "9999-12-31 23:59:59.000 "
[解决办法]
set @s_1 = '00:00:00 '
set @s_2 = '59:59:00 '
set @startTime= '2004-05-26 '
set @endTime = '9999-12-31 '
where surveytime between @starttime and @endtime
surveytime 如果不是DateTime类型转化成datetime类型，这样出的数据是在 '2004-05-26 00：00：00 '之后的数据，相当于 加上@startTime＋ ' '+@s_1
如果datetime类型数据没有时间只有日期 系统会自行加上时间 是 '00:00:00 '
[解决办法]
可以这样：
CONVERT(nchar(19),surveytime,120) between @starttime + ' '+@s_1 and @endtime + ' '+@s_2
格式要一致，不然统计不出数据的。
如set @startTime= '2004-05-26 ' 不能写成set @startTime= '2004-5-26 ' 因为经过CONVERTCONVERT(nchar(19),surveytime,120)变成字符串，系统就会以字符串格式比较。
