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 |
|
nduggan23
Starting Member
42 Posts |
 Posted - 2007-04-27 : 12:50:54
|
| Hi all,trying to get the elapsed time between to date fields. table( id, loggedAt, collectedAt)1, 27/04/2007 01:20:12, 27/04/2007 02:30:322, 27/04/2007 01:20:12, 27/04/2007 11:20:12select id, timeelapsed from tableDays: 0 Time: 1:10:20Days: 1 Time: 10:00:00How can i get this result. i tried datediff and datepart but cant seem to get the result i want. Can anyone help me? CheersN |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-27 : 13:37:03
|
| You actually just subtract them, like any other values:select *, collectedAt - LoggedAt as Difffrom yourtableWhat gets returned is a DateTime, offset from the "base date" of 1/1/1900. You can then calculate the # of days and the hours, minutes and seconds like this:select x.*, DateDiff(dd,0, x.Diff) as Days, DatePart(Hour, x.Diff) as Hours, DatePart(Minute,x.Diff) as Minutes, DatePart(second, diff) as Secondsfrom (above SQL) xYou can then format the days, hours, minutes and seconds returned any way you want at your presentation layer now that you have the values.Some handy functions here, see the part about TimeSpans for more on this:http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
|
|
|
|
|
Date Difference