CODEWITHSUNDEEP
sqlsql-serversql-server-2005timetypes
Nathan H
Nathan H

Reputation: 49371

Store time of the day in SQL

How would you store a time or time range in SQL? It won't be a datetime because it will just be let's say 4:30PM (not, January 3rd, 4:30pm). Those would be weekly, or daily meetings. The type of queries that I need are of course be for display, but also later will include complex queries such as avoiding conflicts in schedule. I'd rather pick the best datatype for that now.

I'm using MS SQL Server Express 2005.

Thanks!

Nathan

Upvotes: 7

Views: 6863

Answers (5)

STW
STW

Reputation: 46366

I would recommend still using a DateTime data type and ignoring the date values--ideally using the static MinDate for SQL (Google it). This will give you the benefits of working with a strongly typed field and the only cost will be a few extra bytes.

As for ranges, store them in two separate columns. Then you can subtract one from the other to determine the difference.

Edit: did some Googling.

  • SQL Server 2008 adds a Time data type, so you might want to consider that.
  • You can use SQL 2005's DateTime type and combine it with the CONVERT function to extract just the HH:MM:SS.MMM
  • Different SQL versions support different minimum dates. You could use a static date that will be supported by all such as 1/1/2000, or you could use SQL 2005's minimum value of 1/1/1753 and append the time values to that startic day

So if you stick with 2005, pick your static date, like 1/1/2000, and store your times on it. So 1m:30s would be 2000-1-1 00:01:30.000, and 1h:15m would be 2000-1-1 01:15:00.000

You can then do Date2 - Date1 and get your result of (1h:15:m - 1m:30s) 2000-01-01 01:13:45.000. CONVERT it and you'll have 1:13:45.

Upvotes: 4

DForck42
DForck42

Reputation: 20357

to me it sounds like you're developing a type of meeting scheduler or something to display the meetings.

i think that i would set it p with 2 columns MeetingStart and MeetingEnd, both as datetime fields. This way, you can determine the length of the meeting, and since you already have the date you can easily use it to display it on a calendar or something.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96572

Personally I would find this a reason to upgrade to 2008 which has a separate time datatype.

Upvotes: 6

tvanfosson
tvanfosson

Reputation: 532465

You might want to consider storing it as an int column representing the number of minutes since midnight. In your entity you could expose this as a TimeSpan (or int) representing the same thing. You'd only need to convert between your display values (time format) and the database value (minutes) in order to perform your queries and this could easily be done in your entity (TimeSpan.TotalMinutes, for example).

Upvotes: 1

schooner
schooner

Reputation: 3077

You could store it as an int as 24 hour time and format as needed.

Or store it as a datetime with some fixed date and remove it as needed for display: Jan 1 2000 4:30PM

I would go with datetime field as it gives you the power of all the datetime related functionality.

Upvotes: 1

Related Questions