SQL Server Performance Forum – Threads Archive

Time data type

The outline of my task is to import csv files in sql table and then using reporting services create reports. 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. I couldn’t find any suitable data type field, so programming is required. Any ideas? Thanks

If you are not going to do any calculation based on the time, then use varchar datatype
Madhivanan Failing to plan is Planning to fail

That’s the problem. I need to select and sum values in the report.

Hi, you can convert it to int while calculating declare @tmptime varchar(5) declare @tmptime1 varchar(5) declare @tmptime2 varchar(5) set @tmptime = 122 set @tmptime1 = 102 set @tmptime2 = 26 select convert(int,@tmptime)+convert(int,@tmptime1)+convert(int,@tmptime2) [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards hsGoswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

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
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>