Tag Archives: Date and time functions

Using TIMEFROMPARTS() – SQL Server 2012

This new function in SQL server 2012 helps in converting time parts to time. The syntax of this function is as follows

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

The range of the parameters for TIMEFROMPARTS is as follows :
Hour -> 0-23.
Minutes -> 0-59.
Seconds -> 0-59.
Fractions -> 0-9999999.
Precision -> 0-7.

Let us understand this with an example.

DECLARE @hour INT, @min INT,@Sec INT,@frac INT;
SET @hour = 13
SET @min = 24
SET @Sec = 22
SET @frac = 45
SELECT TIMEFROMPARTS(@hour,@min,@Sec,@frac,2)

time1

This function requires a valid value for the Hour,Minute, Seconds, Fractions, Precision parameters. If any invalid value is passed then this function will return an error. If a Null value is passed on for the Precision parameter then it generates an error. For other parameters if a Null is passed then the output is also Null.

For example in the below code we pass an invalid value for the Hour parameter

time2

Now lets see what happens when we pass a Null value to the Fraction parameter

time3
 
 
 
 
 
 
   
   
   
      
If we fail to pass any one of the parameters then the following error message is returned.
time4

Using DATEFROMPARTS() – SQL Server 2012

DATEFROMPARTS() returns a date value for the specified year, month, and day.

Let us understand this with an example.

The SQL code for returning day, month and year parameters as a date would probably look something like this

DECLARE @Day INT = 07, @Month int = 03,@Year INT = 2014
SELECT CONVERT(datetime,CONVERT(varchar(10),@Year) + '-' +
CONVERT(varchar(10),@Month) + '-' +
CONVERT(varchar(10),@Day),103) AS TheDate

datetime1Now lets implement DATEFROMPARTS

Here is the code for the same

DECLARE @Day INT = 07,
@Month int = 03,
@Year INT = 2014
SELECT DATEFROMPARTS (@Year, @Month, @Day) AS TheDate

datetime2

EOMONTH() – End of the month – SQL Server 2012

So how do we calculate the last date of the month which is 4 months from now?

SQL Server 2012 presents EOMONTH() function. Let us understand this function with an example.

DECLARE @MyDate datetime
SET @MyDate = GETDATE()
SELECT EOMONTH (@MyDate,4)
SELECT EOMONTH (@MyDate,-4)
as LastDayOfTheMonth

eom2
So what do we find from the 2 outputs. Well it gives me the last date of the 4th month from the current month i.e. 4 months from now would be April and the last date of April is 30th. The second output gives the last date of 4 months previous i.e August. This function would be very handy in calculations where the last date of the month is a critical factor. Previously to achieve this we would have to do a bit of manipulation.

<span>%d</span> bloggers like this: