CODEWITHSUNDEEP
sql-server-2005sql-server-2008types
MadBoy
MadBoy

Reputation: 11114

Time DataType in Sql Server 2005

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.

Upvotes: 2

Views: 17102

Answers (3)

aarona
aarona

Reputation: 37354

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.

Upvotes: 1

Prutswonder
Prutswonder

Reputation: 10074

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.

Upvotes: 4

TomTom
TomTom

Reputation: 62159

  • 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.

Upvotes: 2

Related Questions