表名称Problem
表结构:
id nameID time ProID
1 577 2013-6-5 14:6 NULL
2 NULL 06 5 2013 2:05PM 1
NULL 是无数据 , id 为1 是主 id为2 通过 ProID关联到 ID为1,time的时间在数据库里设置为了nvarchar 存的时候 id为1:是这样存的:
convert(nvarchar,datepart(yyyy,getdate()))+'-'+convert(nvarchar,datepart(MM,getdate()))+'-'+convert(nvarchar,datepart(DD,getdate()))+' '+convert(nvarchar,datepart(HH,getdate()))+':'+convert(nvarchar,datepart(mi,getdate()))
id为2存时间是这样存的:getdate()我现在要直接查询出两个time直接相差几个小时该怎么写语句 ?要不要把time类型换为datetime?在存时间时要不要同一?答者有分!高手快来!
表结构:
id nameID time ProID
1 577 2013-6-5 14:6 NULL
2 NULL 06 5 2013 2:05PM 1
NULL 是无数据 , id 为1 是主 id为2 通过 ProID关联到 ID为1,time的时间在数据库里设置为了nvarchar 存的时候 id为1:是这样存的:
convert(nvarchar,datepart(yyyy,getdate()))+'-'+convert(nvarchar,datepart(MM,getdate()))+'-'+convert(nvarchar,datepart(DD,getdate()))+' '+convert(nvarchar,datepart(HH,getdate()))+':'+convert(nvarchar,datepart(mi,getdate()))
id为2存时间是这样存的:getdate()我现在要直接查询出两个time直接相差几个小时该怎么写语句 ?要不要把time类型换为datetime?在存时间时要不要同一?答者有分!高手快来!
参考 http://msdn.microsoft.com/zh-cn/library/ms189794(v=sql.105).aspx
select datediff("hour",cast(time1 as datetime),cast (time1 as datetime)) from tablea
select a.time as time1,b.time as time2,DATEDIFF(hour,a.time,b.time)
from
(select *,ROW_NUMBER() over(order by createdAt) rn from Problem
)a
inner join (select *,ROW_NUMBER() over(order by createdAt)+1 rn from Problem
)b on a.rn=b.rn
select a.id,a.nameid,datediff(hour,cast(a.time as datetime),cast(b.time as datetime)) as [hour] from problem a join problem b
on a.id=b.proid
http://www.cnblogs.com/insus/archive/2011/01/17/1937683.html