SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Difference between two times in SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

archana23
Yak Posting Veteran

85 Posts

Posted - 06/17/2013 :  14:11:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 06/17/2013 :  14:15:11  Show Profile  Reply with Quote
Instead of plain DATEDIFF, do this:
(DATEDIFF(MINUTE ,'16:10','10:23') + (24*60))%(24*60)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/17/2013 :  14:15:16  Show Profile  Reply with Quote
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?



Edited by - Lamprey on 06/17/2013 14:18:17
Go to Top of Page

shan007
Starting Member

USA
17 Posts

Posted - 06/17/2013 :  16:05:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 06/17/2013 :  17:04:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1770 Posts

Posted - 06/17/2013 :  19:50:17  Show Profile  Reply with Quote
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:
select DATEDIFF(MINUTE ,'16:10',DateAdd(day, 1, '10:23'))
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000