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...
CREATE FUNCTION dbo.TimeSerial (@hrs int, @min int, @sec bigint)
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 ;
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)
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]
SELECT DatePart(hh, CallDateTime) As [Hr],
FLOOR(DatePart(n, CallDateTime)/30) * 30 As [Mi],
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'
GROUP BY Hr, Mi
ORDER BY Hr, Mi
If all is well, you should see these results:
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.
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