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