Getting Date and Time Values From Their Parts in SQL Server 2012

SQL Server provides many functions for getting date and time values from their parts. You can do that easily using the functions DATEFROMPARTS, TIMEFROMPARTS, DATETIMEFROMPARTS etc. in SQL Server 2012. Before SQL Server 2012, you had to do many conversions to get the desired results. In this article, you will see how to get date and time values from their parts in SQL Server 2008 conversion. 

This article explains the following functions to getting date and time values from their parts:

1. DATEFROMPARTS Function
2. TIMEFROMPARTS Function
3. DATETIMEFROMPARTS Function

So let's have a look at a practical example of how to get the date and time values from their parts in SQL Server. The example is developed in SQL Server using the SQL Server Management Studio.

In SQL Server 2008

To get date and time values from their parts in SQL Server 2008:

Declare @Year as int=2013

Declare @Month as int=02

Declare @Day as int=20

Select Convert(Date, Convert(varchar(4), @Year) + '-' + Convert(varchar(2), @Month) + '-' +  Convert(varchar(2), @Day))

Output

In SQL Server 2012

DATEFROMPARTS Function

The DATEFROMPARTS function is used to return a date value for the specified year, month, and day. The syntax of the DATEFROMPARTS built-in date function is as follows:

DATEFROMPARTS ( year, month, day )

All three parameters of the DATEFROMPARTS function are required.

year: Integer expression specifying a year.

 

month: Integer expression specifying a month, from 1 to 12.

day: Integer expression specifying a day.

Example

Declare @Year as int=2013

Declare @Month as int=02

Declare @Day as int=20

 

Select DATEFROMPARTS(@Year, @Month, @Day)

 

Output

2013-02-20

TIMEFROMPARTS Function

The TIMEFROMPARTS function is used to return time values for the specified time and with the specified precision. The syntax of the TIMEFROMPARTS built-in date function is as follows: 

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

If the arguments are invalid, then an error is raised. If any of the parameters are null, null is returned. 

Example

Declare @hour as int=58

Declare @minute as int=46

Declare @seconds as int=20

Declare @fractions as int=0

Declare @precision as int=0

 

Select TIMEFROMPARTS(@hour , @minute , @seconds, @fractions , @precision)
 

Output

58:46:20.0000000

DATETIMEFROMPARTS Function

The DATETIMEFROMPARTS function is used to return a DateTime value for the specified date and time. The syntax of the DATETIMEFROMPARTS built-in date function is as follows: 

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

If the arguments are invalid, then an error is raised. If any of the parameters are null, null is returned. 

Example

Declare @Year as int=2013

Declare @Month as int=12

Declare @Day as int=20

Declare @hour as int=58

Declare @minute as int=46

Declare @seconds as int=0

Declare @milliseconds as int=0 

 

Select DATETIMEFROMPARTS (@Year, @Month, @Day, @hour , @minute , @seconds, @milliseconds)

 

Output

2013-12-20 58:59:46.0000000