T-SQL 日期处理总结

明天放假,今天刚好整理一下,对于日期的格式、推算、运算是平时常用的,简单易错。

1. T-SQL 日期格式转换

由原始的值2012-04-03 10:57:06.127转换成以下20种常用的格式

2012-04-03
20120403
2012.04.03
04/03/2012
03/04/2012
03.04.2012
03-04-2012
03 Apr 2012
Apr 03, 2012
10:57:06
Apr  3 2012
04-03-2012
2012/04/03
20120403
03 Apr 2012
10:57:06:127
2012-4-3
2012年04月03日
2012年4月3日
2012-04-03 10:57:06:127

代码:

   1:  --由2012-04-03 10:23:10.177转换成2012-04-03 
   2:  SELECT GETDATE() AS Val,Convert(char(11),GETDATE(),120) AS NeedVal 
   3:   
   4:  --由2012-04-03 10:23:10.177转换成20120403 
   5:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 112 ) AS NeedVal 
   6:   
   7:  --由2012-04-03 10:23:10.177转换成2012.04.03 
   8:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 102 ) AS NeedVal 
   9:   
  10:  --由2012-04-03 10:23:10.177转换成04/03/2012 月日年
  11:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 101 ) AS NeedVal 
  12:   
  13:  --由2012-04-03 10:23:10.177转换成03/04/2012 日月年
  14:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 103 ) AS NeedVal 
  15:   
  16:  --由2012-04-03 10:23:10.177转换成03.04.2012 日月年
  17:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 104 ) AS NeedVal 
  18:   
  19:  --由2012-04-03 10:23:10.177转换成03-04-2012 日月年
  20:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 105 ) AS NeedVal 
  21:   
  22:  --由2012-04-03 10:23:10.177转换成03 Apr 2012 日月年
  23:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 106 ) AS NeedVal 
  24:   
  25:  --由2012-04-03 10:23:10.177转换成Apr 03, 2012
  26:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 107 ) AS NeedVal 
  27:   
  28:  --由2012-04-03 10:23:10.177转换成10:44:02
  29:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 108 ) AS NeedVal 
  30:   
  31:  --由2012-04-03 10:23:10.177转换成Apr  3 2012 
  32:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 109 ) AS NeedVal 
  33:   
  34:  --由2012-04-03 10:23:10.177转换成04-03-2012
  35:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 110 ) AS NeedVal 
  36:   
  37:  --由2012-04-03 10:23:10.177转换成2012/04/03
  38:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 111 ) AS NeedVal 
  39:   
  40:  --由2012-04-03 10:23:10.177转换成20120403
  41:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 112 ) AS NeedVal 
  42:   
  43:  --由2012-04-03 10:23:10.177转换成03 Apr 2012 
  44:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 113 ) AS NeedVal 
  45:   
  46:  --由2012-04-03 10:23:10.177转换成10:45:45:447
  47:  select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 114 ) AS NeedVal 
  48:   
  49:  --由2012-04-03 10:23:10.177转换成2012-4-3
  50:  SELECT GETDATE() AS Val,REPLACE(CONVERT(varchar(10),GETDATE(),120),N'-0','-') AS NeedVal 
  51:   
  52:  --由2012-04-03 10:23:10.177转换成2012年04月03日
  53:  SELECT GETDATE() AS Val,STUFF(STUFF(CONVERT(char(8),GETDATE(),112),5,0,N'年'),8,0,N'月')+N'日' AS NeedVal 
  54:   
  55:  --由2012-04-03 10:23:10.177转换成2012年4月3日
  56:  SELECT  GETDATE() AS Val,DATENAME(Year,GETDATE())+N'年'+CAST(DATEPART(Month,GETDATE()) AS varchar)+N'月'+DATENAME(Day,GETDATE())+N'日' AS NeedVal 
  57:   
  58:  --由2012-04-03 10:23:10.177转换成2012-04-03 10:23:10.177
  59:  SELECT GETDATE() AS Val,CONVERT(char(11),GETDATE(),120)+CONVERT(char(12),GETDATE(),114) AS NeedVal 

2. T-SQL 日期推算

给定日期,得到该年的第一天和最后一天

给定日期所在季度的第一天和最后一天

给定日期所在月份的第一天或最后一天

给定日期所在周的取星期几的日期,星期天做为一周的第1天

昨天
前天
明天
后天
上一年

下一年

上季度

下季度
上月

下月  
上周

下周

前一小时

后一小时

前一分钟

后一分钟

前一秒

后一秒

代码:

   1:  --给定日期,得到该年的第一天和最后一天
   2:  SELECT CONVERT(char(5),GETDATE(),120)+'1-1' AS FDATE,CONVERT(char(5),GETDATE(),120)+'12-31' LDTAE
   3:   
   4:  --给定日期所在季度的第一天和最后一天,两种方法算最后一天
   5:  SELECT CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,GETDATE())*3-Month(GETDATE())-2,GETDATE()),120)+'1') AS FDATE
   6:      ,CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,GETDATE())*3-Month(GETDATE()),GETDATE()),120)
   7:          +CASE WHEN DATEPART(Quarter,GETDATE()) in(1,4)
   8:              THEN '31'ELSE '30' END) AS LDATE
   9:              
  10:              
  11:  SELECT CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,GETDATE())*3-Month(GETDATE())-2,GETDATE()),120)+'1') AS FDATE
  12:      ,DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,GETDATE())*3-Month(GETDATE()),GETDATE()),120)+'1') AS LDATE
  13:   
  14:  --给定日期所在月份的第一天和最后一天
  15:  SELECT CONVERT(datetime,CONVERT(char(8),GETDATE(),120)+'1') AS FDATE,DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,GETDATE()),120)+'1') AS LDATE
  16:   
  17:  --给定日期所在周的取星期几的日期,星期天做为一周的第1天
  18:  SELECT DATEADD(Day,0-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
  19:  SELECT DATEADD(Day,1-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
  20:  SELECT DATEADD(Day,2-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
  21:  SELECT DATEADD(Day,3-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
  22:  SELECT DATEADD(Day,4-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
  23:  SELECT DATEADD(Day,5-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
  24:  SELECT DATEADD(Day,6-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
  25:   
  26:  --昨天
  27:  SELECT DATEADD(Day,-1,GETDATE())
  28:  --前天
  29:  SELECT DATEADD(Day,-2,GETDATE())
  30:  --明天
  31:  SELECT DATEADD(Day,1,GETDATE())
  32:  --后天
  33:  SELECT DATEADD(Day,2,GETDATE())
  34:   
  35:  --上一年
  36:  SELECT DATEADD(year,-1,GETDATE())
  37:   
  38:  --下一年
  39:  SELECT DATEADD(year,1,GETDATE())
  40:   
  41:  --上季度
  42:  SELECT DATEADD(quarter,-1,GETDATE())
  43:   
  44:  --下季度
  45:  SELECT DATEADD(quarter,1,GETDATE())
  46:   
  47:   --上月
  48:  SELECT DATEADD(month,-1,GETDATE())
  49:   
  50:  --下月
  51:  SELECT DATEADD(month,1,GETDATE())
  52:    
  53:   --上周
  54:  SELECT DATEADD(week,-1,GETDATE())
  55:   
  56:  --下周
  57:  SELECT DATEADD(week,1,GETDATE())
  58:   
  59:   --前一小时
  60:  SELECT DATEADD(hour ,-1,GETDATE())
  61:   
  62:  --后一小时
  63:  SELECT DATEADD(hour ,1,GETDATE())
  64:   
  65:   --前一分钟
  66:  SELECT DATEADD(minute,-1,GETDATE())
  67:   
  68:  --后一分钟
  69:  SELECT DATEADD(minute,1,GETDATE())
  70:   
  71:   --前一秒
  72:  SELECT DATEADD(second ,-1,GETDATE())
  73:   
  74:  --后一秒
  75:  SELECT DATEADD(second ,1,GETDATE())

3. T-SQL日期运算

SELECT DATEDIFF(year, '2012-04-03', '2013-04-03'); --两日期差异年的运算
SELECT DATEDIFF(quarter, '2012-04-03', '2013-04-03'); --两日期差异季度的运算
SELECT DATEDIFF(day, '2012-04-03', '2013-04-03'); --两日期差异日的运算
SELECT DATEDIFF(week, '2012-04-03', '2013-04-03'); --两日期差异周的运算
SELECT DATEDIFF(hour, '2012-04-03', '2013-04-03'); --两日期差异小时的运算
SELECT DATEDIFF(minute, '2012-04-03', '2013-04-03'); --两日期差异分钟的运算
SELECT DATEDIFF(second, '2012-04-03', '2013-04-03'); --两日期差异秒的运算

posted @ 2012-04-03 11:54  谢堂文(Darren Xie)  阅读(3319)  评论(4编辑  收藏  举报