SqlServer时间类型常用技巧

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/a275838263/article/details/50994810


本文主要对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:ss.mmm(24h)
CONVERT(varchar(100), GETDATE(), 126) yyyy-mm-ddThh:mm:ss.mmm(没有空格)
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' 就包含  )



展开阅读全文

没有更多推荐了,返回首页