Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER 2012 -Date and Time Function – DATEFROMPARTS()’

SQL SERVER 2012 -Date and Time Function – DATEFROMPARTS()

Posted in Functions, SQL SERVER, tagged , , on August 31, 2012| 3 Comments »

Before SQL SERVER 2012, If we have a scenario that Year, Month & day are given to us and we need to convert it into date and return it. In this case we need to do a lot of conversion and then we can get our desired results.

For Example :

--SQL Server 2008
Declare @Year as int=2012
Declare @Month as int=12
Declare @Day as int=31

Select Convert(Date,Convert(varchar(4), @Year)
+
Convert(varchar(2), @Month)
+
Convert(varchar(2), @Day))
--Result
2012-12-31

But In SQL Server 2012, the solution came as a new built in function namely DATEFROMPARTS().
Lets discuss this function syntax, parameters, purpose and examples in detail.

Syntax :

DATEFROMPARTS ( year, month, day )

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)

Purpose :
This function requires a valid Year, Month & day as a parameter and returns  a valid date. If we pass any invalid date part to this function it will return an error. Also if we pass NULL to any of its parameter, it returns NULL value.

Let me explain this with simple examples.

Example-1 : DATEFROMPARTS – With valid date parts

Declare @Year as int=2012
Declare @Month as int = 12
Declare @Day as int =31

Select DATEFROMPARTS(@Year,@Month,@Day) as [Result]
--Result

Example-2 : DATEFROMPARTS – With Invalid date parts
If we provide an invalid date part to this function, it will generate an error because only valid date parts are allowed as a parameter.

Declare @Year as int=2012
Declare @Month as int = 13 -- 13 is not a Valid month
Declare @Day as int =31

Select DATEFROMPARTS(@Year,@Month,@Day) as [Result]
--Result

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

Example-3 : DATEFROMPARTS – With NULL Date Parts
If we pass NULL value to any parameter of this function, It will return NULL value.

Declare @Year as int=2012
Declare @Month as int = NULL -- Passed NULL as a Month
Declare @Day as int =31

Select DATEFROMPARTS(@Year,@Month,@Day) as [Result]
--Result

Example-4 : DATEFROMPARTS – With missing parameters
As I have mentioned above that all parameters(3) are mandatory for DATEFROMPARTS function. Lets try to pass less than 3 parameters.

Declare @Year as int=2012
Declare @Month as int = 12
Declare @Day as int =31

Select DATEFROMPARTS(@Year,@Month) as [Result]
--Result

Reference : MSDN

Read Full Post »