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 |
archana23
Yak Posting Veteran
89 Posts |
Posted - 2013-06-17 : 14:11:33
|
Hi,I have two columns in sql named as RegistrationTime and AdmissiionTimeI need to find the difference between these two times. The time between RegistrationTime and AdmissiionTime is not more than 24 hrs. For Example One person registered at yesterday at 16:10 and taken Admission at next day morning 10:23 so i need to find the difference of minutes between those 2 times. I am using below expression to get the differenceselect DATEDIFF(MINUTE ,'16:10','10:23')In above expression given -347 minutes as result.but actual difference is 18hours 13 mins which is 1093 minutes.How do i get this 1093 as result?Can anyone help me on this?Archana |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-17 : 14:15:11
|
Instead of plain DATEDIFF, do this:(DATEDIFF(MINUTE ,'16:10','10:23') + (24*60))%(24*60) |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-06-17 : 14:15:16
|
That is correct. Why do you think the difference is 18+ hours? Are they on different days?EDIT: I missed that one was on the next day.Is there anway to tell that one was on tne next day or is that always going to be the case? |
|
|
shan007
Starting Member
17 Posts |
Posted - 2013-06-17 : 16:05:12
|
You may've to include date along with time in datediff function. If date and time is in different column in your db, then cast them as datetime in datediff function, which will give you exact minutes you want. Refer below sample script, which would bring 1093 sec as expected for your case. Let me know if it helps.declare @dt1 datetimedeclare @dt2 datetimeselect @dt1='20130601 16:10', @dt2='20130602 10:23'select @dt1, @dt2select datediff(MINUTE,@dt1,@dt2)==============================I'm here to learn new things everyday.. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-17 : 17:04:07
|
quote: Originally posted by Lamprey That is correct. Why do you think the difference is 18+ hours? Are they on different days?EDIT: I missed that one was on the next day.Is there anway to tell that one was on tne next day or is that always going to be the case?
Lamprey, the assumption I made in what I posted were:1. The times are less than 24 hours apart (which the OP stated) and,2. Registration time has to be earlier than Admission time, the rationale being that, a patient (or hotel guest) has to register first and only then will he/she be admitted. OP didn't say so, I just assumed. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-06-17 : 19:50:17
|
If you only pass in the TIME portion to the DATEDIFF function, how is the engine supposed to know that you meant two separate days. It has to use the default DATE in its calculation so it arrives at 4:10PM and 10:23AM of the same day. That difference is -347. What you could do is add one day to your second time:[CODE]select DATEDIFF(MINUTE ,'16:10',DateAdd(day, 1, '10:23'))[/CODE]This yields 1093 which you can massage/format as desired.=================================================The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen |
|
|
|
|
|
|
|