Calculate Days, Hours, Mins & Seconds From Two Dates

Hi,

Recently one of our friend posted a question on MSDN. Question was to how to calculate Different between 2 dates in terms of Day, Hour, Month & Seconds. One of our other friend (Santy The Tango Charlie) shared the code. I though of sharing it with you as well.

SET NOCOUNT ON

GO

DECLARE @CDate DATETIME

DECLARE @EDate DATETIME

SET @CDate = getdate()

--SET @CDate = '2011-01-01 10:00:00'

SET @EDate = str(year(@CDate))+'-12-31'

select 'StartDate' = @CDate , 'YearEndDate' = @EDate select 'Months' = DATEDIFF(mm,@CDate,@EDate) , 'Days' = DATEDIFF(dd, DATEADD(mm,(DATEDIFF(mm,@CDate,@EDate)),@CDate) ,@EDate) , 'Hours' = case when (DATEDIFF(hh,@CDate,convert(varchar(11),@EDate+1,101)) - (DATEDIFF(dd,@CDate,@EDate)*24))=24 then '00' else DATEDIFF(hh,@CDate,convert(varchar(11),@EDate+1,101)) - (DATEDIFF(dd,@CDate,@EDate)*24) end , 'Minutes' = case when (abs(DATEDIFF(mi,'01:00:00','00:'+str(substring(convert(varchar(11),@CDate,108),4,2))+':00')))=60 then '00' else abs(DATEDIFF(mi,'01:00:00','00:'+str(substring(convert(varchar(11),@CDate,108),4,2))+':00')) end , 'Seconds' = case when (abs(DATEDIFF(ss,'00:01:00','00:00:'+str(substring(convert(varchar(11),@CDate,108),7,2) ))))=60 then '00' else abs(DATEDIFF(ss,'00:01:00','00:00:'+str(substring(convert(varchar(11),@CDate,108),7,2) )))

end

Original Post http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1aa5e849-043a-4870-8379-8ba6db19be95 Regards Gurpreet Sethi