SQL Server 日期相关资料详细介绍
一、日期类型：对于SQL Server 2008 来说（因为2000甚至2005已经稍微有被淘汰的迹象，所以在此不作过多说明，加上自己工作使用的是2008R2。所以不保证08以前的能用），日期类型有： 数据类型 格式 范围 精确度 存储大小（以字节为单位） 用户定义的秒的小数精度 时区偏移量hh:mm:ss[. nnnnnnn] 00:00:00.0000000 到 23:59:59.9999999 100 纳秒 3 到 5 是 否 YYYY-MM-DD 0001-01-01 到 9999-12-31 1 天 3 无 无 YYYY-MM-DD hh:mm:ss 1900-01-01 到 2079-06-06 1 分钟 4 无 无 YYYY-MM-DD hh:mm:ss[. nnn] 1753-01-01 到 9999-12-31 0.00333 秒 8 无 否 YYYY-MM-DD hh:mm:ss[. nnnnnnn] 0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999 100 纳秒 6 到 8 有 无 YYYY-MM-DD hh:mm:ss[. nnnnnnn] [+|-]hh:mm 0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999（以 UTC 时间表示） 100 纳秒 8 到 10 有 有 注意：所有系统日期和时间值均得自运行 SQL Server 实例的计算机的操作系统。 每一种日期类型有其使用范围，当然以刚好适用为最佳选择，切记不要为了方便，什么都用datetime类型。从性能方面会有很大影响，举个例子： 一个表，有1亿行的数据，有10列日期型（对于一些历史表来说这是完全有可能的）。如果全部使用datetime，那么光这部分的存储空间就是：10*100000000*8字节/(1024*1024)≈7629M≈7.4G,当然，如果有这样的需要，再大也还是要用的，假设其实业务上不需要那么精确（因为datetime是精确到0.00333秒），只需要精确到1分钟即可，那么毫不犹豫使用smalldatetime，可以减少一半的空间，也就是大约3.7G。减少空间的好处有很多，比如备份及数据库文件的大小可以减少，让有限的预算做更多的事情。而且数据页固定8KB，越少的体积单页能存放的数据也就越多，查询时要访问的页面就更少，缓解I/O压力。同时对索引的使用也更有效，等等。 所以这里就能体现出“设计”的重要性。 二、日期函数：日期函数是处理日期的基础，牢记日期函数能减少很多编程工作 精度较高的系统日期和时间函数精确程度取决于运行 SQL Server 实例的计算机硬件和 Windows 版本。标注有：2012有效的是只有2012才出现的功能 函数 语法 返回值 返回数据类型 确定性SYSDATETIME () 返回包含计算机的日期和时间的 datetime2(7)值，SQL Server 的实例正在该计算机上运行。时区偏移量未包含在内。 datetime2(7)不具有确定性 SYSDATETIMEOFFSET ( ) 返回包含计算机的日期和时间的 datetimeoffset(7)值，SQL Server 的实例正在该计算机上运行。时区偏移量包含在内。 datetimeoffset(7)不具有确定性 SYSUTCDATETIME ( ) 返回包含计算机的日期和时间的 datetime2(7)值，SQL Server 的实例正在该计算机上运行。日期和时间作为 UTC 时间（通用协调时间）返回。 datetime2(7)不具有确定性 精度较低的系统日期和时间函数确定性CURRENT_TIMESTAMP 返回包含计算机的日期和时间的 datetime2(7)值，SQL Server 的实例正在该计算机上运行。时区偏移量未包含在内。 datetime不具有确定性 GETDATE ( ) 返回包含计算机的日期和时间的 datetime2(7)值，SQL Server 的实例正在该计算机上运行。时区偏移量未包含在内。 datetime不具有确定性 GETUTCDATE ( ) 返回包含计算机的日期和时间的 datetime2(7)值，SQL Server 的实例正在该计算机上运行。日期和时间作为 UTC 时间（通用协调时间）返回。 datetime不具有确定性 用来获取日期和时间部分的函数确定性DATENAME (datepart,date) 返回表示指定日期的指定datepart的字符串。 nvarchar不具有确定性 DATEPART (datepart,date) 返回表示指定date的指定datepart的整数。 int不具有确定性 DAY (date) 返回表示指定date的“日”部分的整数。 int具有确定性 MONTH (date) 返回表示指定date的“月”部分的整数。 int具有确定性 YEAR (date) 返回表示指定date的“年”部分的整数。 int具有确定性 用来从部件中获取日期和时间值的函数确定性DATEFROMPARTS (year,month,day) 返回表示指定年、月、日的 date值。date具有确定性 DATETIME2FROMPARTS (year,day,hour,minute,seconds,fractions,precision) 对指定的日期和时间返回 datetime2值（具有指定精度）。datetime2(precision)具有确定性 DATETIMEFROMPARTS (year,milliseconds) 为指定的日期和时间返回 datetime值。datetime具有确定性 DATETIMEOFFSETFROMPARTS (year,hour_offset,minute_offset,precision) 对指定的日期和时间返回 datetimeoffset值，即具有指定的偏移量和精度。datetime(precision)具有确定性 SMALLDATETIMEFROMPARTS (year,minute) 为指定的日期和时间返回 smalldatetime值。smalldatetime具有确定性 TIMEFROMPARTS (hour,precision) 对指定的时间返回 time值（具有指定精度）。time(precision)具有确定性 用来获取日期和时间差的函数确定性DATEDIFF (datepart,startdate,enddate) 返回两个指定日期之间所跨的日期或时间datepart边界的数目。 int具有确定性 用来修改日期和时间值的函数确定性DATEADD (datepart,number,date) 通过将一个时间间隔与指定date的指定datepart相加，返回一个新的 datetime值。date参数的数据类型。 具有确定性 EOMONTH (start_date[,month_to_add] ) 返回包含指定日期的月份的最后一天（具有可选偏移量）。 返回类型为start_date的类型或 datetime2(7)。具有确定性 SWITCHOFFSET(DATETIMEOFFSET,time_zone) SWITCHOFFSET更改 DATETIMEOFFSET 值的时区偏移量并保留 UTC 值。 datetimeoffset具有其小数精度：DATETIMEOFFSET具有确定性 TODATETIMEOFFSET (expression,time_zone) TODATETIMEOFFSET 将 datetime2 值转换为 datetimeoffset 值。 datetime2 值被解释为指定 time_zone 的本地时间。 具有datetime参数的小数精度的 datetimeoffset具有确定性 用来设置或获取会话格式的函数确定性@@DATEFIRST 返回对会话进行 SET DATEFIRST 操作所得结果的当前值。 tinyint不具有确定性 SET DATEFIRST {number | @number_var}将一周的第一天设置为从 1 到 7 的一个数字。 不适用 不适用 SET DATEFORMAT {format | @format_var}设置用于输入 datetime或smalldatetime数据的日期各部分（月/日/年）的顺序。不适用 不适用 @@LANGUAGE 返回当前使用的语言的名称。 @@LANGUAGE 不是日期或时间函数。但是，语言设置会影响日期函数的输出。 不适用 不适用 SET LANGUAGE { [ N ] 'language'| @language_var}设置会话和系统消息的语言环境。 SET LANGUAGE 不是日期或时间函数。 但是，语言设置会影响日期函数的输出。 不适用 不适用 sp_helplanguage[ [@language =]'language']返回有关所有支持语言的日期格式的信息。 sp_helplanguage不是日期或时间存储过程。但是，语言设置会影响日期函数的输出。 不适用 不适用 用来验证日期和时间值的函数确定性ISDATE (expression) 确定 datetime或smalldatetime输入表达式是否为有效的日期或时间值。int只有与 CONVERT 函数一起使用，同时指定了 CONVERT 样式参数且样式不等于 0、100、9 或 109 时，ISDATE 才是确定的。 三、日期操作详解：3.1、注意：SQL Server 将 0 解释为 1900 年 1 月 1 日。 3.2、对于一些当前会话需要临时改变日期设置时，可以使用SET关键字改变： SET DATEFIRST { number | @number_var }：
SET DATEFORMAT{format|@format_var}：
设置会话语言：虽然这里是语言，但是会影响日期的格式：SET LANGUAGE Italian; GO SELECT @@DATEFIRST; GO SET LANGUAGE us_english; GO SELECT @@DATEFIRST; 3.3、常用函数操作： 函数及其参数 描述 DAY ( date ) ( day、 date) 返回相同的值。 如果 date 只包含时间部分，则返回值为 1，即基准日YEAR ( date ) ( year,date) 返回相同的值。如果 date 仅包含一个时间部分，则返回值为 1900，即基准年CURRENT_TIMESTAMP 与getdate()相同 DATENAME (datepart,date ) 返回表示指定 date 的指定 datepart 的字符串，DATENAME 可用于选择列表 WHERE、HAVING、GROUP BY 和 ORDER BY 子句中 DATEDIFF (datepart,startdate,enddate ) 返回指定的 startdate 和 enddate 之间所跨的指定 datepart 边界的计数（带符号的整数）。 DATEADD (datepart,number,date ) 指定 number 时间间隔（有符号整数）与指定 date 的指定 datepart 相加后，返回该 date ISDATE ( expression ) 如果 expression 是有效的 date 、time或 datetime 值，则返回 1；否则，返回 0SWITCHOFFSET （DATETIMEOFFSET,time_zone ) 返回从存储的时区偏移量变为指定的新时区偏移量时得到的 datetimeoffset 值 四、常用日期处理案例：这是文章的重点，因为上面大部分内容都可以从联机丛书中查到 给定某个日期，计算相关的值，目前我的工作中遇到比较多的就是这些，至于有些特殊历法所需日期，目前没遇到，所以没总结： 五、建议：创建时间维度表：在本人以前工作中，经常需要查询时间范围（精确到天），此时，可以创建一个表，每一行对应一天，然后其他列就是所需日期，比如季初季末、月初月末、年初年末甚至上年下年等。以供直接调用，并且就算存10年的数据，也就3000多条。有这样需求的可以考虑使用。六、速查手册：日期往往要转换成字符型再进行处理，所以这里贴出部分转换结果代码如下:Select CONVERT(varchar(100),GETDATE(),0)--05 16 2006 10:57AMSelect CONVERT(varchar(100),1)--05/16/06 Select CONVERT(varchar(100),2)--06.05.16 Select CONVERT(varchar(100),3)--16/05/06 Select CONVERT(varchar(100),4)--16.05.06 Select CONVERT(varchar(100),5)--16-05-06 Select CONVERT(varchar(100),6)--16 05 06 Select CONVERT(varchar(100),7)--05 16,06 Select CONVERT(varchar(100),8)--10:57:46 Select CONVERT(varchar(100),9)--05 16 200610:57:46:827AM Select CONVERT(varchar(100),10)--05-16-06 Select CONVERT(varchar(100),11)--06/05/16 Select CONVERT(varchar(100),12)--060516 Select CONVERT(varchar(100),13)--16 05 2006 10:57:46:937 Select CONVERT(varchar(100),14)--10:57:46:967 Select CONVERT(varchar(100),20)--2006-05-16 10:57:47 Select CONVERT(varchar(100),21)--2006-05-16 10:57:47.157 Select CONVERT(varchar(100),22)--05/16/06 10:57:47 AM Select CONVERT(varchar(100),23)--2006-05-16 Select CONVERT(varchar(100),24)--10:57:47 Select CONVERT(varchar(100),25)--2006-05-16 10:57:47.250 Select CONVERT(varchar(100),100)--05 16 2006 10:57AM Select CONVERT(varchar(100),101)--05/16/2006 Select CONVERT(varchar(100),102)--2006.05.16 Select CONVERT(varchar(100),103)--16/05/2006 Select CONVERT(varchar(100),104)--16.05.2006 Select CONVERT(varchar(100),105)--16-05-2006 Select CONVERT(varchar(100),106)--16 05 2006 Select CONVERT(varchar(100),107)--05 16,2006 Select CONVERT(varchar(100),108)--10:57:49 Select CONVERT(varchar(100),109)--05 16 200610:57:49:437AM Select CONVERT(varchar(100),110)--05-16-2006 Select CONVERT(varchar(100),111)--2006/05/16 Select CONVERT(varchar(100),112)--20060516 Select CONVERT(varchar(100),113)--16 05 2006 10:57:49:513 Select CONVERT(varchar(100),114)--10:57:49:547 Select CONVERT(varchar(100),120)--2006-05-16 10:57:49 Select CONVERT(varchar(100),121)--2006-05-16 10:57:49.700 Select CONVERT(varchar(100),126)--2006-05-16T10:57:49.827 Select CONVERT(varchar(100),130)--18 ???? ?????? 142710:57:49:907AM Select CONVERT(varchar(100),131)--18/04/142710:57:49:920AM （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |