<

TimeSerial Function for Microsoft SQL Server

Published on
11,498 Points
5,398 Views
1 Endorsement
Last Modified:
Approved
Kevin Cross
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)
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
;

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],
           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
;

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 > http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_670-DateSerial-Function-for-Microsoft-SQL-Server.html
1
Comment
Author:Kevin Cross
0 Comments

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Join & Write a Comment

Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month