sql server日期时间函数
Sql Server中的日期与时间函数
1 . 当前系统日期、时间
select getdate ()
2 . dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值
例如：向日期加上2天
select dateadd ( day , 2 , ' 2004-10-15 ' ) -- 返回：2004-10-17 00:00:00.000
3 . datediff 返回跨两个指定日期的日期和时间边界数。
select datediff ( day , ' 2004-09-01 ' , ' 2004-09-18 ' ) -- 返回：17
4 . datepart 返回代表指定日期的指定日期部分的整数。
select DATEPART ( month , ' 2004-10-15 ' ) -- 返回 10
5 . datename 返回代表指定日期的指定日期部分的字符串
select datename (weekday, ' 2004-10-15 ' ) -- 返回：星期五
6 . day (), month (), year () -- 可以与datepart对照一下
select 当前日期 = convert ( varchar ( 10 ), getdate (), 120 ) ,当前时间
= convert ( varchar ( 8 ), getdate (), 114 )
select datename (dw, ' 2004-10-15 ' )
select 本年第多少周 = datename (week, ' 2004-10-15 ' ) ,今天是周几
= datename (weekday, ' 2004-10-15 ' )
二、日期格式转换
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608
select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
20040912
select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12
其它我不常用的日期格式转换方法:
select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004
select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004
select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004
select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004
select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004
select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004
select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1
select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004
select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1
select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177
xxxxxxxxxx
--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')*-
--4：获取日期和时间部分的函数
--①datepart()返回表示指定date的指定datepart的整数:in
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'--一周中的第几天39
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 16, 06 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 16, 2006 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 (5, 2) 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)) --输出193.57000 --输出193.57000
--bigint数据类型的字段截取（其它类型也一样） select substring(CONVERT(varchar(15),字段名),11,9) from 表名 select substring(cast(字段名 as varchar(50),6,9)) from 表名
--注意 select substring('13579024681122',1,5) select substring('13579024681122',0,5) --13579 --1357 --该字符串中的每个字符都被认为具有数字位置：第一个字符的位置是 1，第二个字符的位置是 2，依此类推。
|
|
|
|
- | 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(),108) AS '时分秒' --11:24:59 --获得小时数 SELECT CONVERT(VARCHAR(2),GETDATE(),108) AS '时' --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())) --获得当月天数
举例：
1.GetDate() 用于sql server :select GetDate()
2.DateDiff('s','2005-07-20','2005-7-25 22:56:32')返回值为 514592 秒
DateDiff('d','2005-07-20','2005-7-25 22:56:32')返回值为 5 天
3.DatePart('w','2005-7-25 22:56:32')返回值为 2 即星期一(周日为1，周六为7)
DatePart('d','2005-7-25 22:56:32')返回值为 25即25号
DatePart('y','2005-7-25 22:56:32')返回值为 206即这一年中第206天
DatePart('yyyy','2005-7-25 22:56:32')返回值为 2005即2005年
附图
函数 | 参数/功能 |
GetDate( ) | 返回系统目前的日期与时间 |
DateDiff (interval,date1,date2) | 以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1 |
DateAdd (interval,number,date) | 以interval指定的方式，加上number之后的日期 |
DatePart (interval,date) | 返回日期date中，interval指定部分所对应的整数值 |
DateName (interval,date) | 返回日期date中，interval指定部分所对应的字符串名称 |
参数 interval的设定值如下：
值 | 缩 写（Sql Server） | Access 和 ASP | 说明 |
Year | Yy | yyyy | 年 1753 ~ 9999 |
Quarter | q | 季 1 ~ 4 | |
Month | Mm | m | 月1 ~ 12 |
Day of year | Dy | y | 一年的日数,一年中的第几日 1-366 |
Day | Dd | d | 日，1-31 |
Weekday | Dw | w | 一周的日数，一周中的第几日 1-7 |
Week | Wk | ww | 周，一年中的第几周 0 ~ 51 |
Hour | Hh | h | 时0 ~ 23 |
Minute | Mi | n | 分钟0 ~ 59 |
Second | Ss | s | 秒 0 ~ 59 |
Millisecond | Ms | - | 毫秒 0 ~ 999 |
