Getting a valid hour from a number in SQL Server

Getting a valid hour from a number in SQL Server

Getting a valid hour from a number in SQL Server

Is common to deal with special requirement that we need to use some usual date functions for solving the typical problems inside our Database, but in some cases we will have some special requirements and the need to process the date and time datatype make us to go further of common solutions.

In an important process of migration of data, we found that one key column which represented the information related with the time had an integer datatype, in this case was necessary to manipulate the original value with a special time function:

Original value (datatype was integer): 231452
Wanted value: 23:14:52 (twenty three hours, fourteen minutes, fifty two seconds)

We have had since 2012 version in SQL Server one useful Time function for this type of situation, it is TIMEFROMPARTS , in the next link you can find the official information of this.

https://msdn.microsoft.com/en-us/library/hh213398.aspx

The syntax consists in provide the integer value for the next segment

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

In this example we are going to manage a range of valid values, we will put a default zero to fractions and precision because it was not part of the requirement.  I will begin in the inverse order, the first thing that I am posting is the solution and I will try to explain step by step:


DECLARE @t int =231452
SELECT @t / 10000
SELECT (@t / 100) % 100
SELECT @t % 100

SELECT timefromparts(@t / 10000, (@t / 100) % 100, @t % 100, 0, 0)

In this case the integer value is 231452, if you run the T-SQL statement you can see each input parameter for the TIMEFROMPART.   We would get the hour, for this we apply the division:

SELECT @t / 10000

Like @t is an integer datatype then the division returns 23, this is the value corresponding to the Hour and I have got the first parameter.  For the input parameter of minute we apply a combination of operations, this is:

SELECT (@t / 100) % 100

The segment (@t / 100) returns 2314 and from this value when we apply the arithmetic operator % this returns the integer remainder of a division, in short words 2314 % 100 and then the result is: 14 , indeed we have the minutes.

The final step is getting the seconds, we have applied a combination of division and % operator, for the seconds the process is simply the same

SELECT @t % 100

The final result is 52 because this is the remainder of the division of 231452 between 100.  Until this point we have got the desired result with simple arithmetic conversions and the help of an interesting function in SQL Server 2012.

resultsfinal

Enjoy and Good Code !!!!

Leave a Reply

Your email address will not be published. Required fields are marked *