本文主要对SqlServer时间类型常用技巧做一个小小的总结，主要在于时间与字符串的转换处理。
一、SqlServer常用的时间类型
1、sqlserver常见的时间类型有DATE、DATETIME、TIME、TIMESTAMP四种，其中TIMESTAMP与前三者有所不同，timestamp 这种数据类型表现自动生成的二进制数，类似于序列，是自动生成的；当你改变含有timestamp字段的数据时，timestamp会自动更新到最新的操作时间，timestamp设计的初衷估计就是问了记录数据库的改变。
2、在进行mysql、oracle、SqlServer、Sybase四个数据库常用时间格式的一些总结（做时间抽取的时间戳）：（Sybase没有详细的验证）
| 使用下面字段格式来验证时间戳抽取 | |||
| 数据库类型 | 字段类型 | 格式 | 精确度 |
| ORACLE | date | yyyymmdd | 天 |
| yyyymmddhh24miss | 秒 | ||
| varchar2 | yyyymmddhh24miss | 秒 | |
| yyyymmddhh24mi | 分 | ||
| yyyymmddhh | 时 | ||
| yyyymmdd | 天 | ||
| yyyy-mm-dd hh:mi24:ss | 秒 | ||
| yyyy/mm/dd hh:mi24:ss | 秒 | ||
| number | TO_NUMBER(TO_DATE('2005-03-29 12:30:45', 'YYYY-MM-DD HH24:MI:SS')-TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000 | 秒 | |
| SqlServer | date | 年月日 | |
| datetime | 秒 | ||
| datetime2(7) | 秒 | ||
| timestamp | 不能做时间戳(建表时候最好不要这个字段，因为中间库没有对应的字段类型来匹配) | 秒 | |
| time(7) | 不能做时间戳 | 时分秒 | |
| varchar(100) | CONVERT(varchar(100), GETDATE(), 20) | ||
| CONVERT(varchar(100), GETDATE(), 21) | |||
| CONVERT(varchar(100), GETDATE(), 22) | |||
| CONVERT(varchar(100), GETDATE(), 25) | |||
| CONVERT(varchar(100), GETDATE(), 111) | yy/mm/dd | ||
| CONVERT(varchar(100), GETDATE(), 112) | yymmdd | ||
| CONVERT(varchar(100), GETDATE(), 120) | yyyy-mm-dd hh:mi:ss(24h) | ||
| CONVERT(varchar(100), GETDATE(), 121) | yyyy-mm-dd hh:mi:(24h) | ||
| CONVERT(varchar(100), GETDATE(), 126) | yyyy-mm-ddThh:mm:（没有空格） | ||
| bigint | select datediff(mi,'',GETDATE()) | 分 | |
| select datediff(hh,'',GETDATE()) | 时 | ||
| select datediff(day,'',GETDATE()) | 天 | ||
| Mysql | DATE | 年月日 | |
| DATETIME | 秒 | ||
| TIME | 不能做时间戳 | 时分秒 | |
| TIMESTAMP | 秒 | ||
| YEAR | 年 | ||
| varchar(100) | date_format(now(),'%Y%m%d%H%i%S') | 秒 | |
| date_format(now(),'%Y/%m/%d %H:%i:%S') | 秒 | ||
| date_format(now(),'%Y年%m月%d日 %H时%i分%S秒') | 秒 | ||
| date_format(now(),'%Y%m%d%H%i') | 分 | ||
| date_format(now(),'%Y%m%d%H') | 时 | ||
| date_format(now(),'%Y%m%d') | 天 | ||
| int | unix_timestamp(now()) | 秒 | |
| Sybase | date | 年月日 | |
| datetime | 秒 | ||
| smalldatetime | 秒 | ||
| varchar(20) | convert(varchar(20),getdate(),102) -- '2003.11.12' | 年月日 | |
| convert(varchar(20),getdate(),111) --'2003/09/27' | |||
| convert(varchar(20),getdate(),112) --'20030927 | |||
| rtrim(convert(varchar(20),getdate(),102))+' '+(convert(varchar(20),getdate(),108)) -- '2003.11.12 11:03:41' | 秒 | ||
| int | convert(int,convert(varchar(20),getdate(),112)) | 年月日 | |
二、时间与字符格式的互相转化（最常用）
1、如下表，包含各种时间的字符串格式（这个表相当有用）
例如：A、时间转换成字符串：你想把当前的时间，转换成‘2006-02-22’这样的格式，你可以这样做：CONVERT(CHAR(10), getdate(), 23)
这里有一个问题，把当前的时间转换成‘2016-02’时，你会发现下表中没有对应的格式，这个时候，你只需要改一下字符串的长度就可以了：CONVERT(CHAR(7), getdate(), 23).
B、字符串转换成时间：类似于上面，CONVERT(datetime,'11/1/2003',101)
|
|
Feb 22 2006 4:26PM | CONVERT(CHAR(19), CURRENT_TIMESTAMP, 0) |
| 1 | 02/22/06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 1) |
| 2 | 06.02.22 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 2) |
| 3 | 22/02/06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3) |
| 4 | 22.02.06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 4) |
| 5 | 22-02-06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 5) |
| 6 | 22 Feb 06 | CONVERT(CHAR(9), CURRENT_TIMESTAMP, 6) |
| 7 | Feb 22, 06 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 7) |
| 8 | 16:26:08 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 8 ) |
| 9 | Feb 22 2006 4:26:08:020PM | CONVERT(CHAR(26), CURRENT_TIMESTAMP, 9) |
| 10 | 02-22-06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 10) |
| 11 | 06/02/22 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 11) |
| 12 | 060222 | CONVERT(CHAR(6), CURRENT_TIMESTAMP, 12) |
| 13 | 22 Feb 2006 16:26:08:020 | CONVERT(CHAR(24), CURRENT_TIMESTAMP, 13) |
| 14 | 16:26:08:037 | CONVERT(CHAR(12), CURRENT_TIMESTAMP, 14) |
| 20 | 2006-02-22 16:26:08 | CONVERT(CHAR(19), CURRENT_TIMESTAMP, 20) |
| 21 | 2006-02-22 16:26:08.037 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 21) |
| 22 | 02/22/06 4:26:08 PM | CONVERT(CHAR(20), CURRENT_TIMESTAMP, 22) |
| 23 | 2006-02-22 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 23) |
| 24 | 16:26:08 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 24) |
| 25 | 2006-02-22 16:26:08.037 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 25) |
| 100 | Feb 22 2006 4:26PM | CONVERT(CHAR(19), CURRENT_TIMESTAMP, 100) |
| 101 | 02/22/2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 101) |
| 102 | 2006.02.22 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 102) |
| 103 | 22/02/2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103) |
| 104 | 22.02.2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 104) |
| 105 | 22-02-2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 105) |
| 106 | 22 Feb 2006 | CONVERT(CHAR(11), CURRENT_TIMESTAMP, 106) |
| 107 | Feb 22, 2006 | CONVERT(CHAR(12), CURRENT_TIMESTAMP, 107) |
| 108 | 16:26:08 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108) |
| 109 | Feb 22 2006 4:26:08:067PM | CONVERT(CHAR(26), CURRENT_TIMESTAMP, 109) |
| 110 | 02-22-2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 110) |
| 111 | 2006/02/22 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 111) |
| 112 | 20060222 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) |
| 113 | 22 Feb 2006 16:26:08:067 | CONVERT(CHAR(24), CURRENT_TIMESTAMP, 113) |
| 114 | 16:26:08:067 | CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114) |
| 120 | 2006-02-22 16:26:08 | CONVERT(CHAR(19), CURRENT_TIMESTAMP, 120) |
| 121 | 2006-02-22 16:26:08.080 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 121) |
| 126 | 2006-02-22T16:26:08.080 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126) |
| 127 | 2006-02-22T16:26:08.080 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 127) |
| 130 | 24 ???? 1427 4:26:08:080PM | CONVERT(CHAR(32), CURRENT_TIMESTAMP, 130) |
| 131 | 24/01/1427 4:26:08:080PM | CONVERT(CHAR(25), CURRENT_TIMESTAMP, 131) |
一、时间格式相关的计算
时间的计算有很多的函数，这里介绍一些我经常用的
1、当前时间减去4天时间：DATEADD(D,-4,GETDATE())
2、时间计算：DATEDIFF(mm,begintime,endtime)
这里mm代表的是月，例如begintime=2016-08，endtime=2016-09，那么DATEDIFF(mm,'2016-08',2016-09')=1
同理，y表示年，h表示小时，等等。
3、字符串类型的时间，只要格式一样，也是可以进行比较的。例如begintime='2016-08'，endtime='201609',那么begintime<201609=true
四、一个容易出错的地方
在between...and...这个语句中，上限的时间是不包括小时的。
例如：lasttime='2016-05-25 ' begintime='2016-04-25' endtime='2016-05-25 12:12:12'
那么 lasttime就不在between begintime and endtime 这个区间内(如果endtime='2016-05-25' 就包含 )