Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144

    Cool Unanswered: Storing Time Duration data

    i've got an excel spreadsheet generated everyday which stores the duration of talk time as 0:02:09

    When i use the Integration Services Package to move the data into a table. I've used datetime column. It comes up as 1899-12-30 00:00:02:09.000

    What's best practice to store duration as in a SQL Server table? or should i convert it to seconds.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I usually use an tinyint/smallint/int column for this (depending on how long the duration is allowed to be), and not a datetime. if you do this, it's a good idea to add the units onto the column name so it's obvious to others (and yourself!) what the units are, like DurationSec or DurationMinutes, etc.

    IMO datetimes are for storing a point in time, not a duration.

    Incidentally, ms was originally going to ship yukon with a new type called time (and another called date), but those types got cut. so all we have in yukon is (small)datetime. Maybe they'll have them in katmai...

  3. #3
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    Since SQL stores datetime as a data type, the date sets set to a default value when you INSERT the time. If what you are storing is the nu7mber of seconds in the duration, I'd suggest converting to a common unit (e.g., Seconds) and stroing that value in a column of INT or BIGINT. The application can do the formatting for presentation purposes.

    Just a thought...
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

Posting Permissions

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