TimeSerial Function for Microsoft SQL Server

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)
                      AS
                      BEGIN
                          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)
                              BEGIN
                              SET @hours = floor(@total/3600) % 24
                              SET @total = @total % 3600
                              END
                          -- set am/pm based on hours in HH format
                          SET @AMorPM = 'PM'
                          IF @hours < 12
                              BEGIN
                              SET @AMorPM = 'AM'
                              END
                          -- adjust hours to non-military time
                          IF @hours > 12 OR @hours = 0
                              BEGIN
                              SET @hours = abs(@hours - 12)
                              END
                          ;
                      
                          -- calculate the minutes and seconds portion
                          SET @minutes = 0
                          IF (@total >= 60)
                              BEGIN
                              SET @minutes = floor(@total/60)
                              SET @total = @total % 60
                              END
                          ;
                      
                          -- 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 ;
                      END
                      ;

Select all 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)
                      ;

Select all 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],
                                 Customer
                          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
                      ;

Select all 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

Select all 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