Time DataType in Sql Server 2005

Question

I've created small database under SQL Server 2008. Now i wanted to move it to SQL Server 2005 and it doesn't work since it doesn't have Time datatype. What option do I have to store only time in SQL Server 2005. I've written small TimeSheet application which needs to write hours and minutes mostly in format like 05:30:00 (hh:mm:ss) but ss is optional.

Answer 1

• None. Out of the box.
• Do it yourself using a CLR based custom type.
• Live with it.

For a timesheet I personally would prefer NOT to have a time field, but to store time as DateTime always - makes it easier in queries.

Answer 2

SQL Server 2005 does support the DATETIME datatype, which also includes time. To store only the time, you can use the CONVERT statement:

SELECT CONVERT(DATETIME, '11:22:33')

It's not optimal, because the date part is not used, but still takes up storage space. But then again, downgrading a database to a previous version never is optimal.

Answer 3

Sadly, there is no Time datatype in SQL Server 2005. What I would suggest doing to convert your existing data to your SQL Server 2005 Instance is use something like this:

Select CONVERT(DateTime, YourDateTimeColumn, 114) AS [HH:MI:SS:MMM(24H)]
  From YourTable

This will give your the current date WITH the time from the existing data. You will have to just strip the date of the time somehow when retrieving the data. This can be done using a Format function in C# (or whatever .NET language you might be using... assuming you are doing that)

Hope that helps.