Agent Jobs

Find Difference Between Two Dates

Ever find yourself needing to find the difference between two dates, but displaying in such a way that makes sense to most people? Here’s some quick sql logic to get you the difference.

declare @last_date datetime = '2015-09-26 14:00:01.670'

select case
when datediff(second, @last_date, getdate()) / 60 / 60 / 24 / 30 / 12 = 0 then ''
else cast(datediff(second, @last_date, getdate()) / 60 / 60 / 24 / 30 / 12 as nvarchar(50)) + ' Yr ' end +
case
when datediff(second, @last_date, getdate()) / 60 / 60 / 24 / 30 % 12 = 0 then ''
else cast(datediff(second, @last_date, getdate()) / 60 / 60 / 24 / 30 % 12 as nvarchar(50)) + ' Mo ' end +
case
when datediff(second, @last_date, getdate()) / 60 / 60 / 24 % 30 = 0 then ''
else cast(datediff(second, @last_date, getdate()) / 60 / 60 / 24 % 30 as nvarchar(50)) + ' Dy ' end +
case
when datediff(second, @last_date, getdate()) / 60 / 60 % 24 = 0 then ''
else cast(datediff(second, @last_date, getdate()) / 60 / 60 % 24 as nvarchar(50)) + ' Hr ' end +
case
when datediff(second, @last_date, getdate()) / 60 % 60 = 0 then ''
else cast(datediff(second, @last_date, getdate()) / 60 % 60 as nvarchar(50)) + ' mn ' end
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s