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 |
|
meltingchain
Starting Member
3 Posts |
 Posted - 2013-06-21 : 15:41:14
|
| I'm trying to find the difference in hours between SomeTime and Midnight by using dateDiff datediff(mi,Convert(Time,SomeTime), Convert(time,'23:59:59')) However because Converting it into time i cannot use 24:00:00 and if i use 00:00:00 i get a negative number because it calculates backwards. is there a way to get the correct amount, so that when i convert it i wont be losing data. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-21 : 16:01:52
|
quote:
Originally posted by meltingchain
I'm trying to find the difference in hours between SomeTime and Midnight by using dateDiff datediff(mi,Convert(Time,SomeTime), Convert(time,'23:59:59')) However because Converting it into time i cannot use 24:00:00 and if i use 00:00:00 i get a negative number because it calculates backwards. is there a way to get the correct amount, so that when i convert it i wont be losing data.
I didn't quite follow what the problem you are trying to solve is, but see if this does what you are trying to do. Also, dividing by 60 to convert to hours.DATEDIFF(mi,Convert(Time,SomeTime),'19000102')/60.0 |
 |
|
|
meltingchain
Starting Member
3 Posts |
Posted - 2013-06-21 : 16:22:51
|
| When i was trying to find the date difference from someTime (lets say 17:00:00) to midnight i had to use 23:59 but i lost a minute. yes that did fix it thank you. if you could also explain to me why it works ill greatly appreciate it. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-06-21 : 17:14:16
|
1 + datediff(mi,Convert(Time,SomeTime), Convert(time,'23:59:59'))
N 56°04'39.26"E 12°55'05.63" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-22 : 02:45:56
|
| DATEDIFF(mi,YourTimeVariable,1)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Problem with dateDiff