Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
eugz
Posting Yak Master
210 Posts |
 Posted - 2005-07-14 : 17:59:19
|
| Hi All.I have fields StartDate, EndDate in Table1. Format of the fields: mm/dd/yyyy hh:mm:ss PM/AM. I have a problem to create stored procedure to calculate time execution of task. The calculation result I would like to keep in TimeExc field format: hh:mm:ss in Table1.Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-07-14 : 19:07:14
|
| Use DATEDIFF to get the date difference between your two columns. You can add, using DATEADD, say minutes to a generic date such as 1/1/1901 00:00:00 to get the time portion correct. Then use CONVERT with style 108 to get the time portion only.All 3 functions are documented in SQL Server Books Online.Tara |
 |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-07-14 : 20:24:39
|
| Similar to what Tara said (this will consider dates on different days):select convert(varchar, datediff(d, StartDate, EndDate) * 24 + left(convert(char(8), EndDate - StartDate, 108), 2)) + right(convert(char(8), EndDate - StartDate, 108), 6)If you can always be guaranteed that your two dates will fall on the same day, you can just do this instead:select convert(char(8), EndDate - StartDate, 108) |
|
|
|
eugz
Posting Yak Master
210 Posts |
Posted - 2005-07-15 : 09:55:50
|
Thanks a lot. That exactly what I expected.  |
|
|
|
|
|
|
|
|
Calculate time executions