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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Difference between two times in SQL

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 AdmissiionTime
I 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 difference

select 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)
Go to Top of Page

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?


Go to Top of Page

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 datetime
declare @dt2 datetime

select @dt1='20130601 16:10', @dt2='20130602 10:23'
select @dt1, @dt2

select datediff(MINUTE,@dt1,@dt2)

==============================
I'm here to learn new things everyday..
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -