SQL Server Portal

In my previous Posts, I discussed about DATEFROMPARTS and TIMEFROMPARTS function in SQL SERVER 2012. Today we will discuss one more function introduced in SQL SERVER 2012 namely “DATETIMEFROMPARTS“.

Let me explain its syntax, parameters, purpose and examples in detail.

Syntax :

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Parameters :
@Year : A valid integer for year.
@Month : A valid integer for month range from 1-12.
@Day : A valid integer for day range from 1-31. (Depends upon the total number of days in a month)
@hour : A valid integer for the hour range from 0-23
@Minutes : A valid integer for the minute range from 0-59
@Seconds : A valid integer for the second range from 0-59
@milliseconds : A valid integer for the millisecond range from 0-999.

Purpose :
This function requires year, month, day, hour, minute, seconds & milliseconds as a parameter (All parameters are mandatory) and return date and time as a result. If we pass any invalid date and time parts, it will generate an error. Also if we pass NULL date and time parts to this function it will return NULL value.
Let me explain this with simple examples.

Example-1 : DATETIMEFROMPARTS

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @hour as smallint = 23
Declare @Minutes as smallint =58
Declare @Seconds as smallint =55
Declare @milliseconds as smallint =990
Select DATETIMEFROMPARTS ( @Year,@Month,@Day,@hour,@Minutes,@Seconds,@milliseconds)
as [RESULT]
--RESULT
2012-12-31 23:58:55.990

Example-2 : DATETIMEFROMPARTS – With Invalid Date and Time Parts
It will generate an error becuase only valid date and time parts are allowed as a parameter.

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @hour as smallint = 24 --- Not a valid hour
Declare @Minutes as smallint =58
Declare @Seconds as smallint =55
Declare @milliseconds as smallint =990
Select DATETIMEFROMPARTS ( @Year,@Month,@Day,@hour,@Minutes,@Seconds,@milliseconds) as [RESULT]
--RESULT

Msg 289, Level 16, State 3, Line 26
Cannot construct data type datetime, some of the arguments have values which are not valid.

Example-3 : DATETIMEFROMPARTS – With NULL Date and Time Parts
It will return NULL even if only one part is NULL.

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @hour as smallint = NULL --- Lets Make hour as a NULL
Declare @Minutes as smallint =58
Declare @Seconds as smallint =55
Declare @milliseconds as smallint =990
Select DATETIMEFROMPARTS ( @Year,@Month,@Day,@hour,@Minutes,@Seconds,@milliseconds)
as [RESULT]
--RESULT

Reference : MSDN

Read Full Post »