原文:
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
