您现在的位置是：首页 > 文章详情

SQL Server ->> 时间函数： EOMONTH, DATEFROMPARTS, TIMEFROMPARTS, DATETIMEFR...

日期：2018-11-15点击：462收藏

原文: SQL Server ->> 时间函数： EOMONTH, DATEFROMPARTS, TIMEFROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS

上面几个函数都是SQL Server 2012新增的时间函数。

EOMONTH

返回传入时间的月结束日，返回数据类型为DATE

SELECT EOMONTH(GETDATE())

结果为

2016-01-31

DATEFROMPARTS

如同C#或者Java声明一个DATETIME实例那样通过传入YEAR, MONTH, DAY的数字值得到一个DATETIME的实例。这里也是一样。通过传入年月日来得到一个DATE。但是如果你一旦传入的参数无法构造出一个合法的时间，就会报错。

DECLARE @Year int, @Month int, @Day int SET @Year = 2012 SET @Month = 02 SET @Day = 30 SELECT DATEFROMPARTS (@Year, @Month, @Day) AS MyDate

结果就是

Msg 289, Level 16, State 1, Line 61 Cannot construct data type date, some of the arguments have values which are not valid.

如果是合法

DECLARE @Year int, @Month int, @Day int SET @Year = 2012 SET @Month = 02 SET @Day = 28 SELECT DATEFROMPARTS (@Year, @Month, @Day) AS MyDate

那就是

2012-02-28

TIMEFROMPARTS

和DATEFROMPARTS类似，只不过传入的变成HOUR, MINUTE, SECOND,MILLISECOND和MILLISECOND精确位数，然后返回的是一个TIME类型。有一个需要注意的是这个函数的第五个参数是不支持整型变量的，必须是显示常量传入。

比如

DECLARE @Hour int, @Minutes int, @Seconds int, @FractionsOfASecond int SET @Hour = 15 SET @Minutes = 23 SET @Seconds = 47 SET @FractionsOfASecond = 500 SELECT TIMEFROMPARTS(@Hour, @Minutes, @Seconds, @FractionsOfASecond, 3) AS MyTime

结果

15:23:47.500

如果传入一个NULL值呢

DECLARE @Hour int, @Minutes int, @Seconds int, @FractionsOfASecond int SET @Hour = 15 SET @Minutes = 23 SET @Seconds = 47 SET @FractionsOfASecond = 500 SELECT TIMEFROMPARTS(@Hour, @Minutes, @Seconds, NULL, 3) AS MyTime

结果也是NULL

DATETIMEFROMPARTS

这个就是前面两个的结合。特点也就是传入NULL值就是结果变NULL，不合法值就报错。奇怪的是它没有了TIMEFROMPARTS的精确位数参数。

DECLARE @Year int, @Month int, @Day int, @Hour int DECLARE @Minutes int, @Seconds int, @MilliSeconds int SET @Year = 2012 SET @Month = 07 SET @Day = 23 SET @Hour = 17 SET @Minutes = 27 SET @Seconds = 49 SET @MilliSeconds = 0 SELECT DATETIMEFROMPARTS (@Year, @Month, @Day, @Hour, @Minutes, @Seconds, @MilliSeconds) AS MyDateTime

结果

2012-07-23 17:27:49.000

DATETIMEOFFSETFROMPARTS

这个比较有意思。加入了TIMEZONE。

DECLARE @Year int, @Month int, @Day int DECLARE @Hour int, @Minutes int, @Seconds int DECLARE @FractionsOfASecond int DECLARE @HourOffSet int, @MinuteOffSet int SET @Year = 2012 SET @Month = 02 SET @Day = 26 SET @Hour = 15 SET @Minutes = 57 SET @Seconds = 49 SET @FractionsOfASecond = 500 SET @HourOffSet = 7 SET @MinuteOffSet = 30 SELECT DATETIMEOFFSETFROMPARTS (@Year, @Month, @Day, @Hour, @Minutes, @Seconds, @FractionsOfASecond, @HourOffSet, @MinuteOffSet, 3) AS MyTimeZone