[置顶] SQL Server 日期相关

原帖出处： http://blog.csdn.net/dba_huangzj/article/details/7657979

对于开发人员来说，日期处理或许简单，或许很难。结合自己过往的开发经验并整合网上的例子，总结出一些日期相关的操作，供自己备用及为大家分享：

一、日期类型：

对于SQL Server 2008 来说（因为2000甚至2005已经稍微有被淘汰的迹象，所以在此不作过多说明，加上自己工作使用的是2008R2。所以不保证08以前的能用），日期类型有：

数据类型	格式	范围	精确度	存储大小（以字节为单位）	用户定义的秒的小数精度	时区偏移量
time	hh:mm:ss[. nnnnnnn]	00:00:00.0000000 到 23:59:59.9999999	100 纳秒	3 到 5	是	否
date	YYYY-MM-DD	0001-01-01 到 9999-12-31	1 天	3	无	无
smalldatetime	YYYY-MM-DD hh:mm:ss	1900-01-01 到 2079-06-06	1 分钟	4	无	无
datetime	YYYY-MM-DD hh:mm:ss[. nnn]	1753-01-01 到 9999-12-31	0.00333 秒	8	无	否
datetime2	YYYY-MM-DD hh:mm:ss[. nnnnnnn]	0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999	100 纳秒	6 到 8	有	无
datetimeoffset	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	SYSDATETIME ()	返回包含计算机的日期和时间的datetime2(7)值，SQL Server 的实例正在该计算机上运行。时区偏移量未包含在内。	datetime2(7)	不具有确定性
SYSDATETIMEOFFSET	SYSDATETIMEOFFSET ( )	返回包含计算机的日期和时间的datetimeoffset(7)值，SQL Server 的实例正在该计算机上运行。时区偏移量包含在内。	datetimeoffset(7)	不具有确定性
SYSUTCDATETIME	SYSUTCDATETIME ( )	返回包含计算机的日期和时间的datetime2(7)值，SQL Server 的实例正在该计算机上运行。日期和时间作为 UTC 时间（通用协调时间）返回。	datetime2(7)	不具有确定性

精度较低的系统日期和时间函数

函数	语法	返回值	返回数据类型	确定性
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	返回包含计算机的日期和时间的datetime2(7)值，SQL Server 的实例正在该计算机上运行。时区偏移量未包含在内。	datetime	不具有确定性
GETDATE	GETDATE ( )	返回包含计算机的日期和时间的datetime2(7)值，SQL Server 的实例正在该计算机上运行。时区偏移量未包含在内。	datetime	不具有确定性
GETUTCDATE	GETUTCDATE ( )	返回包含计算机的日期和时间的datetime2(7)值，SQL Server 的实例正在该计算机上运行。日期和时间作为 UTC 时间（通用协调时间）返回。	datetime	不具有确定性

用来获取日期和时间部分的函数

函数	语法	返回值	返回数据类型	确定性
DATENAME	DATENAME (datepart,date)	返回表示指定日期的指定datepart的字符串。	nvarchar	不具有确定性
DATEPART	DATEPART (datepart,date)	返回表示指定date的指定datepart的整数。	int	不具有确定性
DAY	DAY (date)	返回表示指定date的“日”部分的整数。	int	具有确定性
MONTH	MONTH (date)	返回表示指定date的“月”部分的整数。	int	具有确定性
YEAR	YEAR (date)	返回表示指定date的“年”部分的整数。	int	具有确定性

用来从部件中获取日期和时间值的函数

函数	语法	返回值	返回数据类型	确定性
DATEFROMPARTS（2012有效）	DATEFROMPARTS (year,month,day)	返回表示指定年、月、日的date值。	date	具有确定性
DATETIME2FROMPARTS（2012有效）	DATETIME2FROMPARTS (year,month,day,hour,minute,seconds,fractions,precision)	对指定的日期和时间返回datetime2值（具有指定精度）。	datetime2(precision)	具有确定性
DATETIMEFROMPARTS（2012有效）	DATETIMEFROMPARTS (year,month,day,hour,minute,seconds,milliseconds)	为指定的日期和时间返回datetime值。	datetime	具有确定性
DATETIMEOFFSETFROMPARTS（2012有效）	DATETIMEOFFSETFROMPARTS (year,month,day,hour,minute,seconds,fractions,hour_offset,minute_offset,precision)	对指定的日期和时间返回datetimeoffset值，即具有指定的偏移量和精度。	datetime(precision)	具有确定性
SMALLDATETIMEFROMPARTS（2012有效）	SMALLDATETIMEFROMPARTS (year,month,day,hour,minute)	为指定的日期和时间返回smalldatetime值。	smalldatetime	具有确定性
TIMEFROMPARTS（2012有效）	TIMEFROMPARTS (hour,minute,seconds,fractions,precision)	对指定的时间返回time值（具有指定精度）。	time(precision)	具有确定性

用来获取日期和时间差的函数

函数

语法

返回值

返回数据类型

确定性

DATEDIFF

DATEDIFF (datepart,startdate,enddate)

返回两个指定日期之间所跨的日期或时间datepart边界的数目。

int

具有确定性

用来修改日期和时间值的函数

函数	语法	返回值	返回数据类型	确定性
DATEADD	DATEADD (datepart,number,date)	通过将一个时间间隔与指定date的指定datepart相加，返回一个新的datetime值。	date参数的数据类型。	具有确定性
EOMONTH（2012）	EOMONTH (start_date[,month_to_add] )	返回包含指定日期的月份的最后一天（具有可选偏移量）。	返回类型为start_date的类型或datetime2(7)。	具有确定性
SWITCHOFFSET	SWITCHOFFSET(DATETIMEOFFSET,time_zone)	SWITCHOFFSET更改 DATETIMEOFFSET 值的时区偏移量并保留 UTC 值。	datetimeoffset具有其小数精度：DATETIMEOFFSET	具有确定性
TODATETIMEOFFSET	TODATETIMEOFFSET (expression,time_zone)	TODATETIMEOFFSET 将 datetime2 值转换为 datetimeoffset 值。 datetime2 值被解释为指定 time_zone 的本地时间。	具有datetime参数的小数精度的datetimeoffset	具有确定性

用来设置或获取会话格式的函数

函数	语法	返回值	返回数据类型	确定性
@@DATEFIRST	@@DATEFIRST	返回对会话进行 SET DATEFIRST 操作所得结果的当前值。	tinyint	不具有确定性
SET DATEFIRST	SET DATEFIRST {number \| @number_var}	将一周的第一天设置为从 1 到 7 的一个数字。	不适用	不适用
SET DATEFORMAT	SET DATEFORMAT {format \| @format_var}	设置用于输入datetime或smalldatetime数据的日期各部分（月/日/年）的顺序。	不适用	不适用
@@LANGUAGE	@@LANGUAGE	返回当前使用的语言的名称。 @@LANGUAGE 不是日期或时间函数。但是，语言设置会影响日期函数的输出。	不适用	不适用
SET LANGUAGE	SET LANGUAGE { [ N ]'language' \| @language_var}	设置会话和系统消息的语言环境。 SET LANGUAGE 不是日期或时间函数。但是，语言设置会影响日期函数的输出。	不适用	不适用
sp_helplanguage	sp_helplanguage[ [@language =]'language']	返回有关所有支持语言的日期格式的信息。 sp_helplanguage不是日期或时间存储过程。但是，语言设置会影响日期函数的输出。	不适用	不适用

用来验证日期和时间值的函数

函数

语法

返回值

返回数据类型

确定性

ISDATE

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 }：

将一周的第一天设置为从 1 到 7 的一个数字。7为默认的第一天，即周日。查看 SET DATEFIRST 的当前设置，请使用@@DATEFIRST函数。注意此值是在运行时执行

SET DATEFORMAT{format|@format_var}：

设置用于解释 date、smalldatetime、datetime、datetime2 和 datetimeoffset 字符串的月、日和年日期部分的顺序。有效参数为 mdy、 dmy、 ymd、 ydm、 myd 和 dym默认值为 mdy。SETDATEFORMAT 将覆盖SETLANGUAGE的隐式日期格式设置。

设置会话语言：虽然这里是语言，但是会影响日期的格式：

SET LANGUAGE Italian;

SELECT @@DATEFIRST;

SET LANGUAGE us_english;

SELECT @@DATEFIRST;

3.3、常用函数操作：

函数及其参数	描述
DAY ( date )	返回一个整数，该整数表示指定的 date 是该月份的哪一天，DAY 与DATEPART( day、 date) 返回相同的值。如果 date 只包含时间部分，则返回值为 1，即基准日
YEAR ( date )	返回一个整数，它表示指定 date 的年份, YEAR 与DATEPART( 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；否则，返回 0
SWITCHOFFSET （DATETIMEOFFSET, time_zone )	返回从存储的时区偏移量变为指定的新时区偏移量时得到的 datetimeoffset 值

四、常用日期处理案例：这是文章的重点，因为上面大部分内容都可以从联机丛书中查到

给定某个日期，计算相关的值，目前我的工作中遇到比较多的就是这些，至于有些特殊历法所需日期，目前没遇到，所以没总结：

--定义给定的一天DECLARE @Date DATETIME = GETDATE();
 
SELECT @Date AS '目前时间'
,DATEADD(DD,-1,@Date) AS '前一天'
,DATEADD(DD,1,@Date) AS '后一天'
/*月计算*/
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'--在SQL Server中0 代表1900-01-01,通过月运算，保证日恒久为1号
,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到天)'--找到下月初再扣减1天，建议使用DATEADD而不要直接“-1”
,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到datetime的小数位)'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天'
,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最后一天'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天'
,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)))  AS '下月最后一天'
/*周计算*/
,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(周日)'--注意此处与@@datefirst的值有关
,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'--注意此处与@@datefirst的值有关
,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'--注意此处与@@datefirst的值有关,其他天数类推
,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期第一天(周日)'--注意此处与@@datefirst的值有关
,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期第一天(星期日)'--注意此处与@@datefirst的值有关
,DATENAME(WEEKDAY,@Date) AS '本日是周几'
,DATEPART(WEEKDAY,@Date) AS '本日是周几'--返回值 1-星期日，2-星期一，3-星期二......7-星期六
/*年度计算*/
,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末'
,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初'
,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末'
/*季度计算*/
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初'
,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'

五、建议：

创建时间维度表：在本人以前工作中，经常需要查询时间范围（精确到天），此时，可以创建一个表，每一行对应一天，然后其他列就是所需日期，比如季初季末、月初月末、年初年末甚至上年下年等。以供直接调用，并且就算存10年的数据，也就3000多条。有这样需求的可以考虑使用。

六、速查手册：日期往往要转换成字符型再进行处理，所以这里贴出部分转换结果

Select CONVERT(varchar(100), GETDATE(), 0)--05 16 2006 10:57AMSelect CONVERT(varchar(100), GETDATE(), 1)--05/16/06Select CONVERT(varchar(100), GETDATE(), 2)--06.05.16Select CONVERT(varchar(100), GETDATE(), 3)--16/05/06Select CONVERT(varchar(100), GETDATE(), 4)--16.05.06Select CONVERT(varchar(100), GETDATE(), 5)--16-05-06Select CONVERT(varchar(100), GETDATE(), 6)--16 05 06Select CONVERT(varchar(100), GETDATE(), 7)--05 16, 06Select CONVERT(varchar(100), GETDATE(), 8)--10:57:46Select CONVERT(varchar(100), GETDATE(), 9)--05 16 200610:57:46:827AMSelect CONVERT(varchar(100), GETDATE(), 10)--05-16-06Select CONVERT(varchar(100), GETDATE(), 11)--06/05/16Select CONVERT(varchar(100), GETDATE(), 12)--060516Select CONVERT(varchar(100), GETDATE(), 13)--16 05 2006 10:57:46:937Select CONVERT(varchar(100), GETDATE(), 14)--10:57:46:967Select CONVERT(varchar(100), GETDATE(), 20)--2006-05-16 10:57:47Select CONVERT(varchar(100), GETDATE(), 21)--2006-05-16 10:57:47.157Select CONVERT(varchar(100), GETDATE(), 22)--05/16/06 10:57:47 AMSelect CONVERT(varchar(100), GETDATE(), 23)--2006-05-16Select CONVERT(varchar(100), GETDATE(), 24)--10:57:47Select CONVERT(varchar(100), GETDATE(), 25)--2006-05-16 10:57:47.250Select CONVERT(varchar(100), GETDATE(), 100)--05 16 2006 10:57AMSelect CONVERT(varchar(100), GETDATE(), 101)--05/16/2006Select CONVERT(varchar(100), GETDATE(), 102)--2006.05.16Select CONVERT(varchar(100), GETDATE(), 103)--16/05/2006Select CONVERT(varchar(100), GETDATE(), 104)--16.05.2006Select CONVERT(varchar(100), GETDATE(), 105)--16-05-2006Select CONVERT(varchar(100), GETDATE(), 106)--16 05 2006Select CONVERT(varchar(100), GETDATE(), 107)--05 16, 2006Select CONVERT(varchar(100), GETDATE(), 108)--10:57:49Select CONVERT(varchar(100), GETDATE(), 109)--05 16 200610:57:49:437AMSelect CONVERT(varchar(100), GETDATE(), 110)--05-16-2006Select CONVERT(varchar(100), GETDATE(), 111)--2006/05/16Select CONVERT(varchar(100), GETDATE(), 112)--20060516Select CONVERT(varchar(100), GETDATE(), 113)--16 05 2006 10:57:49:513Select CONVERT(varchar(100), GETDATE(), 114)--10:57:49:547Select CONVERT(varchar(100), GETDATE(), 120)--2006-05-16 10:57:49Select CONVERT(varchar(100), GETDATE(), 121)--2006-05-16 10:57:49.700Select CONVERT(varchar(100), GETDATE(), 126)--2006-05-16T10:57:49.827Select CONVERT(varchar(100), GETDATE(), 130)--18 ???? ?????? 142710:57:49:907AMSelect CONVERT(varchar(100), GETDATE(), 131)--18/04/142710:57:49:920AM

原帖出处：http://blog.csdn.net/dba_huangzj/article/details/7657979

更多相关文章

随机推荐