asked on

Sql server 2005

How can I difference two date time and show like this "5 houres ago" or "10 minute ago" ?

suppose i have two datetime like

Start Date = 2010-01-22 15:29:55.090
End time = 2010-01-22 16:30:09.153

and I want to show "1 minute ago" format.

OR
Start Date = 2010-01-22 15:29:55.090
End time = 2010-01-24 16:30:09.153

and I want to show "2 daya ago" format.

PortletPaul

the building blocks of this would include:

datediff(day,startDate,endDate)
datediff(hour,startDate,endDate)
datediff(minute,startDate,endDate)

What happens after '59 minutes ago'?
for example, 78 minutes, how is this to be displayed?
or, 27 hours?

I'd suggest some more definition of the requirement may be required

SOLUTION

Om Prakash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dynamicweb09

ASKER

I used this .but still its not working

declare @DateValue datetime
select @DateValue ='2013-05-14 15:29:55.090'

SELECT CASE
WHEN @DateValue-GETDATE() >= 1 THEN CAST(DATEPART(dy, @DateValue-GETDATE()) AS varchar) + ' day(s) remaining'
WHEN @DateValue-GETDATE() >= 0 THEN CAST(DATEPART(hh, @DateValue-GETDATE()) AS varchar) + ' hour(s) remaining'
/*Note, it's convenient to switch around for negatives*/
WHEN GETDATE()-@DateValue <= '00:59' THEN CAST(DATEPART(mi, GETDATE()-@DateValue) AS varchar) + ' minutes(s) overdue'
WHEN GETDATE()-@DateValue <= 1 THEN CAST(DATEPART(hh, GETDATE()-@DateValue) AS varchar) + ' hours(s) overdue'
ELSE
CONVERT(varchar, @DateValue-GETDATE(), 121)
END AS Time_Scale

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dynamicweb09

ASKER

Thanks All............