Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sql server 2005

Posted on 2013-05-16
6
Medium Priority
?
293 Views
Last Modified: 2013-05-16
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.
0
Comment
Question by:dynamicweb09
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39170829
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
0
 
LVL 22

Assisted Solution

by:Om Prakash
Om Prakash earned 200 total points
ID: 39170832
You can do this in PHP as well, please check the following:

http://php.net/manual/en/function.strtotime.php

and in MSSQL, see the following thread

http://stackoverflow.com/questions/1873519/asp-net-and-sql-server-time-difference-in-days-hours-minutes
0
 
LVL 1

Author Comment

by:dynamicweb09
ID: 39170853
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1800 total points
ID: 39170866
maybe this would help?
DECLARE	@START_DATE	DATETIME
DECLARE	@END_DATE	DATETIME
SET		@START_DATE = '2013-05-01 09:00:00'
SET		@END_DATE =   '2013-05-03 11:14:22'

SELECT
  CONVERT(varchar(6), DATEDIFF(day, @START_DATE, @END_DATE))
+ ' days '
+ CONVERT(varchar(6), (DATEDIFF(second, @START_DATE, @END_DATE)/ 3600) % 24)
+ ' hours '
+ RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, @START_DATE, @END_DATE) % 3600) / 60), 2)
+ ' minutes ago'
 AS 'time ago'

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1800 total points
ID: 39170879
so, when dealing with both future and past possibilities, then the datediff calculations need a reversal of dates, like this:
DECLARE	@START_DATE	DATETIME
DECLARE	@END_DATE	DATETIME
SET		@end_DATE = '2013-05-01 09:00:00'
SET		@start_DATE =   '2013-05-03 11:14:22'

SELECT
case when @start_date <= @end_date then
  CONVERT(varchar(6), DATEDIFF(day, @START_DATE, @END_DATE))
+ ' days '
+ CONVERT(varchar(6), (DATEDIFF(second, @START_DATE, @END_DATE)/ 3600) % 24)
+ ' hours '
+ RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, @START_DATE, @END_DATE) % 3600) / 60), 2)
+ ' minutes ago'

else

  CONVERT(varchar(6), DATEDIFF(day, @END_DATE, @START_DATE))
+ ' days '
+ CONVERT(varchar(6), (DATEDIFF(second, @END_DATE, @START_DATE)/ 3600) % 24)
+ ' hours '
+ RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, @END_DATE, @START_DATE) % 3600) / 60), 2)
+ ' minutes to go'

end

 AS 'time string'

Open in new window

see: http://sqlfiddle.com/#!3/1fa93/6823
0
 
LVL 1

Author Closing Comment

by:dynamicweb09
ID: 39170906
Thanks All............
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question