代码改变世界

简单实用SQL脚本Part2:日期和时间函数

2010-04-21 18:25  听风吹雨  阅读(2908)  评论(0编辑  收藏  举报

一、SQL Server 日期函数集合

--参考http://msdn.microsoft.com/zh-cn/library/ms186724.aspx
--
1:获取系统日期和时间值函数
--
getdate()
SELECT GETDATE() AS 'today'
--getutcdate()
SELECT GETUTCDATE() AS 'today'


--2:修改日期和时间值函数
--
dateadd()
SELECT DATEADD(yy,10,GETDATE())
--获取当前天的前后五天日期:
select DATEADD(dd,5,GETDATE())
select DATEADD(dd,-5,GETDATE())
--2008? switchoffset
--
SELECT SWITCHOFFSET ('1998-09-20 7:45:50.71345 -5:00', '-08:00') 
--
2008? todatetimeoffset


--3:获取日期和时间差函数
--
datediff()
SELECT DATEDIFF(yy,'1984/5/3',GETDATE())
--正常使用
SELECT DATEDIFF(HOUR,'1984/5/3',GETDATE())
--转换成正数(负负得正)
SELECT DATEDIFF(MONTH,GETDATE(),'1984/5/3')*-1


--4:获取日期和时间部分的函数
--
①datepart()返回表示指定date的指定datepart的整数:int
SELECT DATEPART(yy,GETDATE()),DATEPART(yyyy,GETDATE()) as 'year'
SELECT DATEPART(mm,GETDATE()),DATEPART(m,GETDATE()) as 'month'
SELECT DATEPART(dd,GETDATE()),DATEPART(d,GETDATE()) as 'day'
SELECT DATEPART(hh,GETDATE()) as 'Hour'
SELECT DATEPART(mi,GETDATE()),DATEPART(n,GETDATE()) as 'minute'
SELECT DATEPART(ss,GETDATE()),DATEPART(s,GETDATE()) as 'second'
SELECT DATEPART(ms,GETDATE()) as 'millisecond'
--others
SELECT DATEPART(DW,GETDATE()),DATEPART(dw,GETDATE()),DATEPART(w,GETDATE()) as 'weekday'--一周中的第几天
SELECT DATEPART(weekday, getdate() + @@DateFirst - 1)--中国星期算法中一周中的星期几
SELECT DATEPART(weekday, getdate() - 1)--中国星期算法中一周中的星期几
SELECT DATEPART(ww,GETDATE()),DATEPART(wk,GETDATE()) as 'week'--?
SELECT DATEPART(dy,GETDATE()),DATEPART(y,GETDATE()) as 'dayofyear'--一年中的第几天
SELECT DATEPART(qq,GETDATE()),DATEPART(q,GETDATE()) as 'quarter'--季度
SELECT DATEPART(qq,'2010-03-21'),DATEPART(q,'2010-04-01'as 'quarter'
--年(yy),季(q),月(m),周(ww),时期(w),天(d),时(hh),分(n),秒(s)

--②datename()返回表示指定日期的指定datepart的字符串:nvarchar
SELECT DATENAME(weekday,GETDATE())--星期三
SELECT DATENAME(WW, GETDATE())--?

--③year(),相当于 datepart(yy,时间)
SELECT YEAR(GETDATE())

--④month(),相当于datepart(mm,时间)
SELECT MONTH(GETDATE())

--⑤day(),相当于datepart(dd,时间)
SELECT DAY(GETDATE())
SELECT DAY('1984/5/3')


--5:验证日期和时间值的函数
SELECT ISDATE('04/15/2008'); --Returns 1.
SELECT ISDATE('15/04/2008'); --Returns 0.

--2008?
--
SELECT CONVERT (date, GETDATE());
--
SELECT CONVERT (time, GETDATE());

 

参数说明:

日期部分

缩写

Year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

Day

dd, d

Week

wk, ww

Weekday

dw

Hour

hh

Minute

mi, n

Second

ss, s

Millisecond

ms

 

二、Date Time 样式 

--语句及查询结果:
SELECT CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
SELECT CONVERT(varchar(100), GETDATE(), 1): 05/16/06
SELECT CONVERT(varchar(100), GETDATE(), 2): 06.05.16
SELECT CONVERT(varchar(100), GETDATE(), 3): 16/05/06
SELECT CONVERT(varchar(100), GETDATE(), 4): 16.05.06
SELECT CONVERT(varchar(100), GETDATE(), 5): 16-05-06
SELECT CONVERT(varchar(100), GETDATE(), 6): 16 05 06
SELECT CONVERT(varchar(100), GETDATE(), 7): 05 1606
SELECT CONVERT(varchar(100), GETDATE(), 8): 10:57:46
SELECT CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
SELECT CONVERT(varchar(100), GETDATE(), 10): 05-16-06
SELECT CONVERT(varchar(100), GETDATE(), 11): 06/05/16
SELECT CONVERT(varchar(100), GETDATE(), 12): 060516
SELECT CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
SELECT CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
SELECT CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
SELECT CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
SELECT CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
SELECT CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
SELECT CONVERT(varchar(100), GETDATE(), 24): 10:57:47
SELECT CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
SELECT CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
SELECT CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
SELECT CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
SELECT CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
SELECT CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
SELECT CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
SELECT CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
SELECT CONVERT(varchar(100), GETDATE(), 107): 05 162006
SELECT CONVERT(varchar(100), GETDATE(), 108): 10:57:49
SELECT CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
SELECT CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
SELECT CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
SELECT CONVERT(varchar(100), GETDATE(), 112): 20060516
SELECT CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
SELECT CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
SELECT CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
SELECT CONVERT(varchar(100), GETDATE(), 121): 2006

 

--对上面进行动态生成字符串:
declare @sql1 nvarchar(200),@sql2 nvarchar(200)
declare @count nvarchar(100);
set @sql1 = 'SELECT CONVERT(varchar(100), GETDATE(), 0)'
set @sql2 = 'SELECT @count = CONVERT(varchar(100), GETDATE(), 0)'
exec sp_executesql  @sql2,N'@count nvarchar(50) out',@count out
print @sql1 +''+ @count

 

--SQL Server 仅保证往返转换(即从原始数据类型进行转换后又返回原始数据类型的转换)在各版本间产生相同值。
DECLARE @myval decimal (52)
SET @myval = 193.57
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))
--输出.57000
--
输出.57000

 

--bigint数据类型的字段截取(其它类型也一样)
select substring(CONVERT(varchar(15),字段名),11,9from 表名
select substring(cast(字段名as varchar(50),6,9)) from 表名

 

--注意
select substring('13579024681122',1,5)
select substring('13579024681122',0,5)
--13579
--
1357
--
该字符串中的每个字符都被认为具有数字位置:第一个字符的位置是1,第二个字符的位置是2,依此类推。

 

参数说明:

不带世纪数位 (yy) (1)

带世纪数位 (yyyy)

标准

输入/输出 (3)

-

0  100 (1,2)

默 认

mon dd yyyy hh:miAM(或 PM

1

101

美 国

mm/dd/yyyy

2

102

ANSI

yy.mm.dd

3

103

英 国/法国

dd/mm/yyyy

4

104

德 国

dd.mm.yy

5

105

意 大利

dd-mm-yy

6

106(1)

-

dd mon yy

7

107(1)

-

mon dd, yy

8

108

-

hh:mi:ss

-

9  109 (1,2)

默 认设置 + 毫秒

mon dd yyyy hh:mi:ss:mmmAM(或 PM

10

110

美 国

mm-dd-yy

11

111

日 本

yy/mm/dd

12

112

ISO

yymmdd

yyyymmdd

-

13  113 (1,2)

欧 洲默认设置 + 毫秒

dd mon yyyy hh:mi:ss:mmm(24h)

14

114

-

hh:mi:ss:mmm(24h)

-

20  120 (2)

ODBC 规范

yyyy-mm-dd hh:mi:ss(24h)

-

21  121 (2)

ODBC 规范(带毫秒)

yyyy-mm-dd hh:mi:ss.mmm(24h)

-

126 (4)

ISO8601

yyyy- mm-ddThh:mi:ss.mmm(无空格)

-

127(6, 7)

带时区 Z ISO8601

yyyy-mm-ddThh:mi:ss.mmmZ

(无 空格)

-

130 (1,2)

回历 (5)

dd mon yyyy hh:mi:ss:mmmAM

-

131 (2)

回历 (5)

dd/mm/yy hh:mi:ss:mmmAM

 

--CONVERT 与 DATEPART
--
获取时分秒
SELECT CONVERT(VARCHAR(20),GETDATE(),108AS '时分秒'
--11:24:59

--获得小时数
SELECT CONVERT(VARCHAR(2),GETDATE(),108AS ''
--11

--其实在获取小时数还有更好的办法
SELECT DATEPART(hh,GETDATE()) as 'Hour'
--11 

 

三、获取前一天或者前一个月日期字符串

--方法一
--
年月日
select convert(varchar(8),getdate()-1,112)
--年月
--
?没有办法
--

SELECT day(getdate()-1)


--方法二
--
年月日
select convert(varchar(8),dateadd(day,-1,'2010-06-01 14:41:04.893'),112)
--年月
select convert(varchar(6),dateadd(month,-1,'2010-01-01 14:41:04.893'),112)
--
select day(dateadd(dd,-1,'2010-06-01 14:41:04.893'))

 

四、获得当月天数 

--获得当月天数
select day(dateadd(mm,1,getdate())-day(getdate())) 

--分析如下:
select getdate() --当前日期
select day(getdate()) --目前第几天
select getdate()-day(getdate()) --上个月最后一天
select dateadd(mm,1,getdate())-day(getdate()) --加上一个月
select day(dateadd(mm,1,getdate())-day(getdate())) --获得当月天数

 

五、计算SQL的执行时间  

--获取select * from TableName语句的执行时间
declare @timediff datetime 
select @timediff=getdate() 
select * from TableName
print '1耗时:'+ convert(varchar(10),datediff(ms,@timediff,getdate()))

 

六、参考文献

 日期和时间数据类型及函数 (Transact-SQL)

 Sqlserver常用函数例子说明

 SQL Server 日期函数集合DATEADD,DATEDIFF,DATENAME,DATEPART