首页 / MYSQL / SQLDateTime函数使用示例

SQLDateTime函数使用示例

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQLDateTime函数使用示例，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5592字，纯文字阅读大概需要8分钟。

内容图文

文章总结了关于DateTime函数用法与数据转换以及简单的参考手册。

在T-SQL中经常会用到与DateTime相关的函数，现将常用函数做一下汇总，以备忘。

们经常出于某种目的需要使用各种各样的日期格式，当然我们可以使用字符串操作来构造各种日期格式,但是有现成的函数为什么不用呢？

SQL Server中文版的默认的日期字段datetime格式是yyyy-mm-dd Thh:mm:ss.mmm

例如:

select getdate()

2004-09-12 11:06:08.177

整理了一下SQL Server里面可能经常会用到的日期格式转换方法:

SQL DAY() – MONTH( ) – YEAR()

DAY('2008-09-30') = 30

MONTH('2008-09-30') = 9

YEAR('2008-09-30') = 2008

SQL DATEPART()

DATEPART(day, '2008-09-30 11:35:00.1234567') = 30

DATEPART(month, '2008-09-30 11:35:00.1234567') =9

DATEPART(year, '2008-09-30 11:35:00.1234567') = 2008

DATEPART(hour, '2008-09-30 11:35:00.1234567') = 11

DATEPART(minute, '2008-09-30 11:35:00.1234567') = 35

DATEPART(second, '2008-09-30 11:35:00.1234567') = 0

DATEPART(quarter, '2008-09-30 11:35:00.1234567') = 3

DATEPART(dayofyear, '2008-09-30 11:35:00.1234567') =273

DATEPART(week, '2008-09-30 11:35:00.1234567') = 40

DATEPART(weekday, '2008-09-30 11:35:00.1234567') =7

DATEPART(millisecond, '2008-09-30 11:35:00.1234567') =123

DATEPART(microsecond, '2008-09-30 11:35:00.1234567') = 123456

DATEPART(nanosecond, '2008-09-30 11:35:00.1234567') = 123456700

DATEPART(tzoffset, '2008-09-30 11:35:00.1234567 -07:00') = -420

SQL DATEADD()

DATEADD(day, 1, '2008-09-30 11:35:00') = 2008-10-30 01:35:00.000

DATEADD(month, 1, '2008-09-30 11:35:00') = 2008-10-30 11:35:00.000

DATEADD(year, 1, '2008-09-30 11:35:00') = 2009-09-30 11:35:00.000

DATEADD(hour, 1, '2008-09-30 11:35:00') = 2008-09-30 12:35:00.000

DATEADD(minute, 1, '2008-09-30 11:35:00') = 2008-09-30 11:36:00.000

DATEADD(second, 1, '2008-09-30 11:35:00') = 2008-09-30 11:35:01.000

DATEADD(quarter, 1, '2008-09-30 11:35:00') =2008-12-30 11:35:00.000

DATEADD(week, 1, '2008-09-30 11:35:00') = 2008-10-07 11:35:00.000

DATEADD(month, -1, '2008-09-30 11:35:00') = 2008-08-30 11:35:00.000

DATEADD(year, 1.5 , '2008-09-30 11:35:00') = 2009-09-30 11:35:00.000

SQL DATENAME()

DATENAME(day, '2008-09-30 11:35:00.1234567') = 30

DATENAME(month, '2008-09-30 11:35:00.1234567') =September

DATENAME(year, '2008-09-30 11:35:00.1234567') = 2008

DATENAME(hour, '2008-09-30 11:35:00.1234567') = 11

DATENAME(minute, '2008-09-30 11:35:00.1234567') = 35

DATENAME(second, '2008-09-30 11:35:00.1234567') = 0

DATENAME(quarter, '2008-09-30 11:35:00.1234567') = 3

DATENAME(dayofyear, '2008-09-30 11:35:00.1234567') =273

DATENAME(week, '2008-09-30 11:35:00.1234567') = 40

DATENAME(weekday, '2008-09-30 11:35:00.1234567') =Saturday

DATENAME(millisecond, '2008-09-30 11:35:00.1234567') =123

DATENAME(microsecond, '2008-09-30 11:35:00.1234567') = 123456

DATENAME(nanosecond, '2008-09-30 11:35:00.1234567') = 123456700

DATENAME(tzoffset, '2008-09-30 11:35:00.1234567 -07:00') = -07:00

SQL DATEDIFF()

DATEDIFF(day, '2007-12-01' , '2008-09-30') = 303

DATEDIFF(month, '2007-12-01' , '2008-09-30') = 9

DATEDIFF(year, '2007-12-01' , '2008-09-30') = 1

DATEDIFF(hour, '06:46:45' , '11:35:00') = 5

DATEDIFF(minute, '06:46:45' , '11:35:00') = 289

DATEDIFF(second, '06:46:45' , '11:35:00') = 17295

DATEDIFF(quarter, '2007-12-01' , '2008-09-30') = 3

DATEDIFF(week, '2007-12-01' , '2008-09-30') = 44

DATEDIFF(hour, '2008-09-30' , '2007-12-01') = -303

SOME OTHER SQL DATE/TIME RELATED FUNCTIONS

GETDATE()

GETUTCDATE()

SYSDATETIME()

SYSUTCDATETIME()

SYSUTCDATETIMEOFFSET()

DATEADD(datepart,NUMBER,date)

DATEADIFF(datepart, startdate,enddate)

TODATETIMEOFFSET(datetime2,tzoffset)

SWITCHOFFSET(datetimeoffset,tzoffest)

ISDATE(expression)

Adding to this - Calculate no of Days between two dates excluding Weekends.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2010/05/01'
SET @EndDate = '2010/05/11'

SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1)-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) -- 7

Without century (yy) With century (yyyy)
Standard
Input/Output**
- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - Mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 (*) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
- 130* Kuwaiti dd mon yyyy hh:mi:ss:mmmAM
- 131* Kuwaiti dd/mm/yy hh:mi:ss:mmmAM

举例如下:

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

内容总结

以上是互联网集市为您收集整理的SQLDateTime函数使用示例全部内容，希望文章能够帮你解决SQLDateTime函数使用示例所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/543586.html

来源：【匿名】