How to get date difference in Day/hour/min/secs pl

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to get date difference in Day/hour/min/secs pl

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2009-08-19 : 17:32:54

in DoctorChart table i have DocName,Id#,Citcotype,Medtype,AdmitDate,DischargeDate(both admitdate & Discharge date is date formatted columns)

so how can i get the below differenceTime (which means Dischargedate-Admit Date in below format i.e, Day,Hours,Minutes,Seconds...)

DoctorName, ID #, Citco type, MEDtype, DifferenceTime
========= === ======= ======= ===========
Dr. KindaEmesko, 20045, Replace OutCard, ICH, 0 day 9 hours 11 minutes
Dr. KindaEmesko, 20098, Replace OutCard, ICH, 1 day 2 hours 34 minutes
Dr. KindaEmesko, 20678, Replace OutCard, ICH, 2 day 23 hours 52 minutes
Dr. KindaEmesko, 20212, Replace OutCard, ICH, 4 day 1 hours 00 minutes
Dr. KindaEmesko, 20345, Replace OutCard, BED, 3 days 14 hours 15 minutes
Dr. KindaEmesko, 20678, Replace OutCard, BED, 9 days 21 hours 52 minutes
Dr. KindaEmesko, 20015, Signature Overlay, Rest, 0 days 3 hours 29 minutes
Dr. KindaEmesko, 45678, Signature Overlay, Rest, 0 days 1 hours 29 minutes

Same way how can i get the Average Time for the above result (without ID columns)

DoctorName, Citco type, MEDtype, Avg DifferenceTime
========= === ======= ======= ===========
Dr. KindaEmesko, Replace OutCard, ICH, 2 day 1 hours 00 minutes
Dr. KindaEmesko, Replace OutCard, BED, 6 days 17 hours 15 minutes
Dr. KindaEmesko, Signature Overlay, Rest, 0 days 2 hours 29 minutes

Please just tell me how to get the two dates difference as day/hour/minute format
as well as how to get avg ( two dates difference as day/hour/minute format)

Please Help me

Thanks in advance
dhani

dhani
Posting Yak Master

132 Posts

Posted - 2009-08-19 : 17:58:01

Especially Average one please.....,

dhani
Posting Yak Master

132 Posts

Posted - 2009-08-19 : 18:51:48

here is the answer it may helpful to others

TO_CHAR(TRUNC((c.DischargeDate-c.AdmitDate))) || ' Days, ' ||
TO_CHAR(TRUNC(MOD((c.DischargeDate-c.AdmitDate)*24,24))) || ' Hours, ' ||
TO_CHAR(TRUNC(MOD((c.DischargeDate-c.AdmitDate)*24*60,60))) || ' Minutes' ||
TO_CHAR(TRUNC(MOD((c.DischargeDate-c.AdmitDate)*24*60*60,60))) || '
================= FOR AVERAGE
TO_CHAR(TRUNC(AVG(c.DischargeDate-c.AdmitDate))) || ' Days, ' ||
TO_CHAR(TRUNC(MOD(AVG(c.DischargeDate-c.AdmitDate)*24,24))) || ' Hours, ' ||
TO_CHAR(TRUNC(MOD(AVG(c.DischargeDate-c.AdmitDate)*24*60,60))) || ' Minutes' ||
TO_CHAR(TRUNC(MOD(AVG(c.DischargeDate-c.AdmitDate)*24*60*60,60))) || ' Secs' as twocol

Thanks & Best Regards

Dhani

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-19 : 22:32:52

you do know that this is a Microsoft SQL Server forum ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources