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 |
|
jhermiz
3564 Posts |
 Posted - 2006-01-20 : 15:08:35
|
I have 2 dates that include the time portions:...DATEADD(minute, (t1.CLOCKIN+30)/60, t1.TheDay) AS TheTimeIN,DATEADD(minute, (t2.CLOCKOUT+30)/60, t2.TheDay) AS TheTimeOUT,...I need to take TheTimeOut - TheTimeIn and yield just the time portion which should include hours and minutes...If I datediff it:DateDiff(???, DATEADD(minute, (t1.CLOCKIN+30)/60, t1.TheDay), DATEADD(minute, (t2.CLOCKOUT+30)/60, t2.TheDay))What could I place in ??? to get just the time (h:mi)Thanks,Jon
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-01-20 : 15:45:05
|
| You should use minutes in your DateDiff(??? to get the TotalMinutes. After that, you can calculate the hours and minutes directly:TotalHours = TotalMinutes/60 LeftOverMinutes = TotalMinutes%60CODO ERGO SUM |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-21 : 05:28:13
|
| I presume that t1.TheDay and t2.TheDay are [potentially] different?Otherwise I reckon its just the difference of CLOCKIN and CLOCKOUT.Maybe its just the difference of those (converted/rounded to minutes) [negative result allowed] plus DateDiff(Minute, t1.TheDay, t2.TheDay) - which might require less CPU horsepower?Kristen |
|
|
|
|
|
|
|
|
time