Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003

    Unanswered: Times in Sql Server

    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.

    Last edited by Bobba Buoy; 08-29-04 at 10:04.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Think marathons! Always enter your times with hours (even when timing 100 meters) and you should get exactly the behavior that you want!


  3. #3
    Join Date
    Apr 2003
    Great tip! Thanks!!!!

  4. #4
    Join Date
    Oct 2003
    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?) 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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts