--当天的0点0分0秒，当天的23点59分59秒
select convert(datetime,convert(varchar(10),getdate(),120)) as a,
dateadd(ss,-1,dateadd(dd,1,convert(datetime,convert(varchar(10),getdate(),120)))) as b


--当前时间所在的周一0点0分0秒  当前时间所在的周日23点59分59秒
select  dateadd(wk,datediff(wk,0,getdate()),0) as a,
dateadd(day,6,dateadd(wk,datediff(wk,0,getdate()),0)) as b,
dateadd(ss,-1,dateadd(dd,1,convert(datetime,convert(varchar(10),dateadd(day,6,dateadd(wk,datediff(wk,0,getdate()),0)),120)))) as c


--当前时间所在的月初0点0分0秒  当前时间所在的月末23点59分59秒
 select  dateadd(month, datediff(month, 0, getdate()), 0) as a,
 dateadd(month, datediff(month, 0, dateadd(month, 1, getdate())), -1) as b,
 dateadd(ss,-1,dateadd(dd,1,convert(datetime,convert(varchar(10),dateadd(month, datediff(month, 0, dateadd(month, 1, getdate())), -1),120)))) as c 


--当前时间所在的年初0点0分0秒  当前时间所在的年尾23点59分59秒
 select  dateadd(year,datediff(year,0,getdate()),0) as a,
 dateadd(year,datediff(year,-1,getdate()),-1) as b,
 dateadd(ss,-1,dateadd(dd,1,convert(datetime,convert(varchar(10),dateadd(year,datediff(year,-1,getdate()),-1),120)))) as c