TimeSerial Function for Microsoft SQL Server

Kevin CrossChief Technology Officer
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
Have you ever had a need to group data by time of day?

If so, you probably had to deal with the fact that you can easily get hour and minute values from your date and timestamp column; however, your display will be in military time which is not as desirable to some users wanting to see values like "1:00 pm" or "11:35 am".  The challenge is that if you convert your date and timestamp data upfront, you will have a hard time sorting times of day appropriately as you will get alphanumeric sort versus chronological.

There are simpler ways to handle grouping and sorting by time I am sure; however, since my development of this function was done over time dealing with a more complex issue, the focus here is on recreating and using the TimeSerial Microsoft Access function in SQL Server as the solution.

As a short background, TimeSerial takes in a number of hours, minutes, and seconds and returns the appropriate time hh:mm:ss AM/PM format.  In Access, the function can additionally do calculations for you based on varying inputs and use of negatives.  

If you want more information on how TimeSerial functions, see reference for function in Microsoft Access below.

Moving right along for those with short attention spans...

The Code
CREATE FUNCTION dbo.TimeSerial (@hrs int, @min int, @sec bigint)
                      RETURNS nvarchar(10)
                          DECLARE @result nvarchar(10), @total bigint, @AMorPM nvarchar(2)
                          DECLARE @hours int, @minutes int, @seconds int
                          -- convert everything to seconds handling null params with isnull or coalesce
                          SET @total = IsNull(@sec,0) + IsNull(@min,0) * 60 + IsNull(@hrs,0) * 3600
                          SET @total = 86400 + @total % 86400 -- handle negative time relative to midnight
                          -- calculate the hour portion
                          SET @hours = 0
                          IF (@total >= 3600)
                              SET @hours = floor(@total/3600) % 24
                              SET @total = @total % 3600
                          -- set am/pm based on hours in HH format
                          SET @AMorPM = 'PM'
                          IF @hours < 12
                              SET @AMorPM = 'AM'
                          -- adjust hours to non-military time
                          IF @hours > 12 OR @hours = 0
                              SET @hours = abs(@hours - 12)
                          -- calculate the minutes and seconds portion
                          SET @minutes = 0
                          IF (@total >= 60)
                              SET @minutes = floor(@total/60)
                              SET @total = @total % 60
                          -- set seconds to remainder
                          SET @seconds = @total
                          SET @result = Cast(@hours As nvarchar(2)) + ':' 
                              + RIGHT('0'+Cast(@minutes As nvarchar(2)), 2)
                          SET @result = @result + ':' 
                              + RIGHT('0'+Cast(@seconds As nvarchar(2)),2) + @AMorPM
                          RETURN @result ;

Open in new window

Hopefully the logic speaks for itself and is relatively straight-forward to follow (please leave comments below if you want explanations).  

And to use it:
SELECT dbo.TimeSerial(14, 30, 0)

Open in new window

Example usage above would return 2:30:00PM.

So you do something like this with your data.  In my original case, I was answering a question for someone regarding a customer calls report.  In that case we only wanted on the hour or half past, so additional math for that is demonstrated.

SELECT dbo.TimeSerial(Hr, Mi, 0) As [Hour], Count(*) AS [NoOfCalls]
                      FROM (
                          SELECT DatePart(hh, CallDateTime) As [Hr],
                                 FLOOR(DatePart(n, CallDateTime)/30) * 30 As [Mi],
                          FROM (
                              SELECT '2009-01-01 14:02:23' AS CallDateTime, 'Fred' AS Customer
                              UNION SELECT '2009-02-01 15:43:13', 'Barney'
                              UNION SELECT '2009-04-01 00:13:15', 'Wilma'
                              UNION SELECT '2009-04-01 12:13:15', 'Betty'
                              UNION SELECT '2009-04-01 23:12:19', 'Bam Bam'
                          ) CustomerCalls
                      ) CallsByTime
                      GROUP BY Hr, Mi
                      ORDER BY Hr, Mi

Open in new window

If all is well, you should see these results:

12:00:00AM	1
                      12:00:00PM	1
                      2:00:00PM 	1
                      3:30:00PM 	1
                      11:00:00PM	1

Open in new window

All nicely sorted into correct chronological order.  And that as they say is that.

We have successfully recreated TimeSerial in MS SQL Server and provided a new tool for ensuring data is not just accurate, but reader friendly.

Happy coding!

Best regards,

Kevin (aka MWVisa1)

References / Related Articles:
TimeSerial in MS Access >  http://office.microsoft.com/en-us/access/HA012289251033.aspx
DateSerial in MS SQL >  https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_670-DateSerial-Function-for-Microsoft-SQL-Server.html
Kevin CrossChief Technology Officer
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.