Time data type | SQL Server Performance Forums

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. <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
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,<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 />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami<br />
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 @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)