关于时间(续)

[tim_spac]

set nocount on
declare @dt datetime
set @dt = getdate()

declare @dt1 datetime, @dt2 datetime
set @dt1 = dateadd(hour,datediff(hour,0,@dt),0) 
set @dt2 = dateadd(ms,-3, dateadd(hour,datediff(hour,0,@dt)+1,0) )
print '当时: ' + convert(varchar,@dt1,121) + ' ~ ' + convert(varchar,@dt2,121) 

set @dt1 = dateadd(day,datediff(day,0,@dt),0) 
set @dt2 = dateadd(ms,-3, dateadd(day,datediff(day,0,@dt)+1,0) )
print '当日: ' + convert(varchar,@dt1,121) + ' ~ ' + convert(varchar,@dt2,121) 

set @dt1 = dateadd(week,datediff(week,0,@dt),0) 
set @dt2 = dateadd(ms,-3, dateadd(week,datediff(week,0,@dt)+1,0) )
print '当周: ' + convert(varchar,@dt1,121) + ' ~ ' + convert(varchar,@dt2,121) 

set @dt1 = dateadd(month,datediff(month,0,@dt),0) 
set @dt2 = dateadd(ms,-3, dateadd(month,datediff(month,0,@dt)+1,0) )
print '当月: ' + convert(varchar,@dt1,121) + ' ~ ' + convert(varchar,@dt2,121) 

set @dt1 = dateadd(quarter,datediff(quarter,0,@dt),0) 
set @dt2 = dateadd(ms,-3, dateadd(quarter,datediff(quarter,0,@dt)+1,0) )
print '当季: ' + convert(varchar,@dt1,121) + ' ~ ' + convert(varchar,@dt2,121) 

set @dt1 = dateadd(year,datediff(year,0,@dt),0) 
set @dt2 = dateadd(ms,-3, dateadd(year,datediff(year,0,@dt)+1,0) )
print '当年: ' + convert(varchar,@dt1,121) + ' ~ ' + convert(varchar,@dt2,121) 
go
-- 当时: 2008-04-14 16:00:00.000 ~ 2008-04-14 16:59:59.997
-- 当日: 2008-04-14 00:00:00.000 ~ 2008-04-14 23:59:59.997
-- 当周: 2008-04-14 00:00:00.000 ~ 2008-04-20 23:59:59.997
-- 当月: 2008-04-01 00:00:00.000 ~ 2008-04-30 23:59:59.997
-- 当季: 2008-04-01 00:00:00.000 ~ 2008-06-30 23:59:59.997
-- 当年: 2008-01-01 00:00:00.000 ~ 2008-12-31 23:59:59.997

[shi0526]

学习

[girl5201314]

datediff
dateadd

[sweetweiwei]

学习

[-狙击手-]

go past

[kelph]

路过

[-狙击手-]

select dateadd(wk,datediff(wk,0,getdate()),0)

[-狙击手-]

select dateadd(wk,datediff(wk,0,getdate()),0) -1

[jimogaoshou]

不错！！！！！收藏

[zccmy22]

嗯。挺不错的。楼主，可以再多整理点类似的真的不错。

[hjj841020]

不错！！！！！

[rockyvan]

這個收藏。

[wangxuelid]

post