x

Get the Time Between two times

I have two times First and Last ..I need a query that shows the time difference and if the GETDATE() =now then it should say In progress. Like if it is a day before then it should say '12 hours 10 Min left' and if it is happening now it should say 'In progress' and if it has passed then it should state 'Passes'

I have tries a few queries but I am not getting the desired result.

SELECT (DATEDIFF(dd, First_Half_Start, GETDATE())) - (CASE WHEN DATEDIFF(dd,0,First_Half_Start) = 0 THEN 1 ELSE 0 END) from Match_Schedule

Thank you !

more ▼

asked Sep 14, 2013 at 02:41 AM in Default

avatar image

fashraf
538 17 21 29

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I think the basic logic would check for your three possible states - before, during or after, then return the state in english

    select
             -- we are after the event - Passes (maybe 'Passed' ?)
         case when getdate() > Last then 'Passes' end 
              -- the event is ongoing - In Progress
         when getdate() between First and Start then 'In Progress' end
             -- we are before the event - parse the timespan to english
         when getdate() < Start then <parse your time difference into english> end as TimeDifference 
     from Match_Schedule

For parsing the time span into english, check out the Working with Time Spans and Durations in SQL Server article on SQLTeam - http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

The crux of the article is this list of calculations:

 TotalSeconds / 3600 as Hours, 
 (TotalSeconds % 3600) / 60 as Minutes, 
 TotalSeconds % 60 as Seconds

See more at: http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server#sthash.pXyZBZZm.dpuf

more ▼

answered Sep 15, 2013 at 03:07 AM

avatar image

KenJ
25k 3 10 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2188
x35

asked: Sep 14, 2013 at 02:41 AM

Seen: 3202 times

Last Updated: Sep 15, 2013 at 03:46 PM

Copyright 2017 Redgate Software. Privacy Policy