I want to enter times for running event performances (like 10:26.35 for a person's 2-mile time, for instance). I am using sql server 2000. This is an ongoing concern for me since I do a lot of work with running events. Can someone give me some direction as to how I can best deal with this?
The datetime data types do a lot of converting to 'time-of-day' and that is not what I want. For instance, if I enter a 2-mile time of 10:26.35 (a pretty typical 2-mile time) my db converts it to 10:26:35 AM.
If I use varchar data type there is a ton of code to write when I want to sort the data.
This is almost-exactly how an SQL server deals with times and dates: it uses integers (or floating-point). It stores the value in terms of the smallest unit: seconds, or days.
This simple technique not-only defeats the grim prophecies of the "Y2K" pundits... (Remember them, and how much havoc and FUD they briefly caused and sought to profit by?) ...it also makes date/time math into "simple math" and easily copes with international preferences. It's simply a matter of input and output conversion.
A time of "10:26.35" is "626.35 seconds." It's trivial to write a function, if one does not already exist, to translate the value. And of course, a TIME or DATETIME field type may (and probably does) have all the functionality you need.