SQL Server Performance

Time data type

Discussion in 'SQL Server DTS-Related Questions' started by dimmil, Aug 18, 2005.

  1. dimmil New Member

    The outline of my task is to import csv files in sql table and then using reporting services create reports. <br />My problem is that values in csv files have the following time format xx<img src='/community/emoticons/emotion-12.gif' alt=':x' />x<img src='/community/emoticons/emotion-12.gif' alt=':x' />x or sometimes xxx<img src='/community/emoticons/emotion-12.gif' alt=':x' />x<img src='/community/emoticons/emotion-12.gif' alt=':x' />x where x=0,1,2...9. For example 122:59:59 which means 122 hours, 59 minutes, 59 secs.<br />I couldn't find any suitable data type field, so programming is required. Any ideas?<br /><br />Thanks
  2. Madhivanan Moderator

    If you are not going to do any calculation based on the time, then use varchar datatype


    Failing to plan is Planning to fail
  3. dimmil New Member

    That's the problem. I need to select and sum values in the report.
  4. ghemant Moderator

    Hi,<br />you can convert it to int while calculating <br /><br />declare @tmptime varchar(5)<br />declare @tmptime1 varchar(5)<br />declare @tmptime2 varchar(5)<br />set @tmptime = 122<br />set @tmptime1 = 102<br />set @tmptime2 = 26<br /><br />select convert(int,@tmptime)+convert(int,@tmptime1)+convert(int,@tmptime2)<br /><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />hsGoswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami<br />
  5. FrankKalis Moderator

    If you're going to measure some duration it might be a good idea to use three separated columns to store hours, minutes and seconds. If needed you can also transform these values into one DATETIME column and do your calculations from there. That might have the advantage that you can use SQL Server's sophisticated date and time handling functions. The transformation might look something like this:

    DECLARE @hours INT
    DECLARE @minutes INT
    DECLARE @seconds INT
    SELECT @hours = 122, @minutes = 59, @seconds = 59
    SELECT DATEADD(second,@hours * 3600 + @minutes * 60 + @seconds, 0)

    1900-01-06 02:59:59.000

    (1 row(s) affected)

    Which way you choose depends on what your reports are supposed to do.

    Frank Kalis
    Microsoft SQL Server MVP
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Share This Page