SqlServer时间类型常⽤技巧
本⽂主要对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不能做时间戳(建表时候最好不要这个字段,因为中间库没有对应的字段类型来匹配)秒
unix时间戳转换日期格式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:(24h) CONVERT(varchar(100), GETDATE(), 126)
yyyy-mm-
ddThh:(没
有空格)
有空格)
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)
102/22/06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 1)
206.02.22CONVERT(CHAR(8), CURRENT_TIMESTAMP, 2)
322/02/06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3)
422.02.06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 4) 522-02-06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 5) 622 Feb 06CONVERT(CHAR(9), CURRENT_TIMESTAMP, 6)
7Feb 22, 06CONVERT(CHAR(10), CURRENT_TIMESTAMP, 7) 816:26:08CONVERT(CHAR(8), CURRENT_TIMESTAMP, 8 )
9Feb 22 2006 4:26:08:020PM CONVERT(CHAR(26), CURRENT_TIMESTAMP, 9) 1002-22-06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 10) 1106/02/22CONVERT(CHAR(8), CURRENT_TIMESTAMP, 11) 12060222CONVERT(CHAR(6), CURRENT_TIMESTAMP, 12) 1322 Feb 2006 16:26:08:020CONVERT(CHAR(24), CURRENT_TIMESTAMP, 13) 1416:26:08:037CONVERT(CHAR(12), CURRENT_TIMESTAMP, 14) 202006-02-22 16:26:08CONVERT(CHAR(19), CURRENT_TIMESTAMP, 20) 212006-02-22 16:26:08.037CONVERT(CHAR(23), CURRENT_TIMESTAMP, 21) 2202/22/06 4:26:08 PM CONVERT(CHAR(20), CURRENT_TIMESTAMP, 22) 232006-02-22CONVERT(CHAR(10), CURRENT_TIMESTAMP, 23) 2416:26:08CONVERT(CHAR(8), CURRENT_TIMESTAMP, 24) 252006-02-22 16:26:08.037CONVERT(CHAR(23), CURRENT_TIMESTAMP, 25) 100Feb 22 2006 4:26PM CONVERT(CHAR(19), CURRENT_TIMESTAMP, 100) 10102/22/2006CONVERT(CHAR(10), CURRENT_TIMESTAMP, 101) 1022006.02.22CONVERT(CHAR(10), CURRENT_TIMESTAMP, 102) 10322/02/2006CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103) 10422.02.2006CONVERT(CHAR(10), CURRENT_TIMESTAMP, 104) 10522-02-2006CONVERT(CHAR(10), CURRENT_TIMESTAMP, 105) 10622 Feb 2006CONVERT(CHAR(11), CURRENT_TIMESTAMP, 106) 107Feb 22, 2006CONVERT(CHAR(12), CURRENT_TIMESTAMP, 10
7) 10816:26:08CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108) 109Feb 22 2006 4:26:08:067PM CONVERT(CHAR(26), CURRENT_TIMESTAMP, 109) 11002-22-2006CONVERT(CHAR(10), CURRENT_TIMESTAMP, 110) 1112006/02/22CONVERT(CHAR(10), CURRENT_TIMESTAMP, 111) 11220060222CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) 11322 Feb 2006 16:26:08:067CONVERT(CHAR(24), CURRENT_TIMESTAMP, 113) 11416:26:08:067CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114) 1202006-02-22 16:26:08CONVERT(CHAR(19), CURRENT_TIMESTAMP, 120) 1212006-02-22 16:26:08.080CONVERT(CHAR(23), CURRENT_TIMESTAMP, 121) 1262006-02-22T16:26:08.080CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126) 1272006-02-22T16:26:08.080CONVERT(CHAR(23), CURRENT_TIMESTAMP, 127) 13024 1427 4:26:08:080PM CONVERT(CHAR(32), CURRENT_TIMESTAMP, 130)
13124/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
四、⼀个容易出错的地⽅
在这个语句中,上限的时间是不包括⼩时的。
例如:lasttime='2016-05-25 ' begintime='2016-04-25' endtime='2016-05-25 12:12:12'
那么 lasttime就不在between begintime and endtime 这个区间内(如果endtime='2016-05-25' 就包含 )
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论