sql中有关时间的操作

--系统日期
select getdate()
--星期
select Datepart(weekday, getdate() + @@DateFirst - 1)
 
--日期转换
select convert(datetime,'2013-06-01 14:36')

--获取距离现在的天数
select  Datediff(day,CONVERT(DATETIME,RDT),getdate()) as dayCount from Sys_SMS

--本周  0本周 1上一周 2前两周
select RDT,datediff(week,RDT,getdate()) from Sys_SMS

--本月 0本月 1上一月  2前两月
select RDT, datediff(month,CONVERT(DATETIME,RDT),getdate())  from Sys_SMS

--case when语句
select RDT,Datediff(day,CONVERT(DATETIME,RDT),getdate()) as dayCount,
case
when Datediff(day,CONVERT(DATETIME,RDT),getdate()) =0  -- 今天
then '今天'
when Datediff(day,CONVERT(DATETIME,RDT),getdate()) =1  --昨天
then '昨天'
when Datediff(day,CONVERT(DATETIME,RDT),getdate()) =2   --前天 ，星期几
then ''
when Datediff(day,CONVERT(DATETIME,RDT),getdate()) =3   --大前天，星期几
then  '大前天'
when datediff(week,RDT,getdate())=0 and Datediff(day,CONVERT(DATETIME,RDT),getdate()) >3
then '本周之内'
when datediff(week,RDT,getdate())=1
then '二周之内'
when  datediff(week,RDT,getdate())=2
then '三周之内'
when datediff(month,CONVERT(DATETIME,RDT),getdate()) = 0
then '本月之内'
when datediff(month,CONVERT(DATETIME,RDT),getdate()) = 1
then '上月之内'
when 60<Datediff(day,CONVERT(DATETIME,RDT),getdate())
then '更早'
end as 'LX'
from Sys_SMS  order by LX