SQL Server: Most optimal way of storing time (without date)

Question

Here's one more or less for perfection's sake.

Microsoft SQL Server only contains the field type datetime for storing dates and times.

But let's say that I want to store a list of business hours, where the date is completely irrelevant. Currently I'm using the datetime type and then simply displaying the time-portion of the data. But I have two problems with this.

1. It seems awkwardly inefficient.
2. It may confuse future developers to see a full-blown date coming along with the time, which they may not know whether is used anywhere or not.

And so it begs the question; in the absence of a specific time field (like in MySQL), what is the most optimal way of storing only a particular time of day, from 00:00 to 23:59?

UPDATE: It's SQL Server 2005. (Also I'd just be interested in knowing what to do in general when there is no time type.)

Answer 1

For SQL Server 2005 or older...

If you only want to know to the minute, you can store it as an int in the range of 1-1440. 1 is 00:01 and 1440 is 0:00.

It would be easy do display as a time again if you like:

SELECT CAST((605 / 60) as varchar) + ':' + RIGHT('0' + CAST((605 % 60) as varchar), 2)

An additional advantage of this is that if you use a smallint data type you are saving 1-3 bytes per record from the built-in TIME datatype.

TIME uses 3-5 bytes per row and smallint is 2 bytes per row.

The extra bytes are for seconds and fractional seconds I believe.

EDIT

It's more complicated with seconds but still doable I should think...

1-86400 range (seconds per day)

DECLARE @i INT
SET @i = 3661

SELECT RIGHT('0' + CAST((@i / 3600) as varchar),2) --hours
+ ':' + RIGHT('0' + CAST((@i % 3600)/60 as varchar), 2) -- minutes
+ ':' + RIGHT('0' + CAST((@i % 3600)%60 as varchar), 2) -- seconds

Answer 2

SQL Server 2008 has a TIME datatype:

http://www.sql-server-performance.com/2007/datetime-2008/

DECLARE @dt as TIME
SET @dt = getdate()
PRINT @dt

Upgrade to SQL 2008?