Solved

DATEDIFF function

Posted on 2010-08-19
13
708 Views
Last Modified: 2012-05-10
Hello All,

How do I write T-SQL to calculate if a machine was used with in 24 hours from the last used date and getdate()?

SELECT DATEDIFF(hh, max(LastUsedDate), GETDATE()) FROM Table1. This gives me only in hours but it does not calculate minutes and seconds.

How would I make sure the mm and ss are also taken into consideration?

Thanks,
-B
0
Comment
Question by:BrookK
  • 3
  • 3
  • 2
  • +4
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>This gives me only in hours but it does not calculate minutes and seconds.
that is by specifications, only the difference in hours.

so: 12:01 and 11:59 are "1 hour" apart, while 12:01 and 12:03 are 0 hours apart...
see the doc: http://msdn.microsoft.com/en-us/library/ms189794.aspx
>Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.<

so:
>How would I make sure the mm and ss are also taken into consideration?
it depends on what return values you need exactly, based on sample data.
please clarify
0
 
LVL 10

Expert Comment

by:Alan_White
Comment Utility
I've not tested this but something like this may do what you want?

SELECT * FROM table WHERE DATEADD(hh,24,LastUsedDate) < {fn now()}
0
 
LVL 7

Expert Comment

by:jmiller47
Comment Utility
SELECT DATEDIFF(hh, max(LastUsedDate) AS HOURS,DATEDIFF(mm, max(LastUsedDate) AS MINUTES,DATEDIFF(ss, max(LastUsedDate) AS SECONDS, GETDATE()) FROM Table1

I hope this is what you are looking for...
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
The user has used the machine in the last 24 hours

SELECT User, Max(LastUsedDate) as MaxLastUsedDate
FROM Table1
group by User
Having DATEADD(hh, 24, Max(LastUsedDate)) > getdate()
0
 
LVL 6

Expert Comment

by:LCSandman8301
Comment Utility
if you want a list of machines that were used in the past 24 hours then it should be in the where clause


select tb.*

from (

  select max(lastused) as lastused, machinename

  from table1

  group by machinename

) as tb

where tb.lastused > datediff(hh, -24, getdate())

Open in new window

0
 

Author Comment

by:BrookK
Comment Utility
For example:

select DATEDIFF(hh, '2010-08-18 08:06:00.000',  '2010-08-19 08:04:00.000') AS DaysDiff
It gives me 24 hours even though there are 2 minutes less for 24 hours between these two date ranges.

It is fine if it gives 23 hours but the accurate answer is 23 hours and 58 minutes.

Thanks,
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 6

Expert Comment

by:LCSandman8301
Comment Utility
then what you are probably looking for is to get the datediff by seconds then to display the answer differently:
select machinename, secondsago/3600 as hoursago, (secondsago%3600)/60 as minutesago, (secondsago%3600)%60 as secondsago

from

(

select machinename, datediff(ss, lastused, getdate()) as secondsago

from table1

) t1

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
if all times are < 24 hours:
select convert(char(8), cast( '2010-08-19 08:04:00.000' as datetime ) - cast('2010-08-18 08:06:00.000' as datetime), 8) AS DaysDiff
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
if there are >= 24 hours, you want the day portion:
declare @s varchar(30)

declare @e varchar(30)

set @s = '2010-08-18 08:04:00.000'

set @e = '2010-08-19 08:02:00.000'

select case when cast(  @e as datetime ) - cast( @s as datetime) < 1 then ''

  else cast( cast( cast( @e as datetime ) - cast(@s as datetime) as int) as varchar(10)) + ' ' end

+ convert(char(8), cast( @e as datetime ) - cast(@s as datetime), 8)

AS DaysDiff



set @s = '2010-08-18 08:04:00.000'

set @e = '2010-09-19 08:02:00.000'

select case when cast(  @e as datetime ) - cast( @s as datetime) < 1 then ''

  else cast( cast( cast( @e as datetime ) - cast(@s as datetime) as int) as varchar(10)) + ' ' end

+ convert(char(8), cast( @e as datetime ) - cast(@s as datetime), 8)

AS DaysDiff





output:

23:58:00

32 23:58:00

Open in new window

0
 

Author Comment

by:BrookK
Comment Utility
I do not want this specific LCS. I just have to add a check for one machine that can I multiple clean events. I have to get the max clean event and see if that is less than or more than current date? If less then error.

Thanks,
0
 
LVL 9

Expert Comment

by:sas13
Comment Utility
--create table table1 (mashine varchar(10), LastUsedDate datetime)

select mashine, convert(varchar(10), getdate() - max(LastUsedDate), 8) as datedifftime
from table1
group by mashine
having (getdate() - max(LastUsedDate)) <= cast('23:59.999' as datetime)
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
Comment Utility
Brook

Maybe you have missed my comment, but using dateadd(hh,-24  is the only correct way to go exactly 24 hours back to the past.  Datediff doesn't cut it.

http:#a33474774

SELECT max(LastUsedDate)
FROM Table1
HAVING max(LastUsedDate) > DATEADD(hh, -24, getdate())

Dateadd(hh, -24, getdate()) takes you 24 hours (to the millisecond) backwards, which you can compare against the max-lastuseddate.  Because MAX is an aggregate, you can either use HAVING, or subquery it and use WHERE.
0
 

Author Closing Comment

by:BrookK
Comment Utility
Soln worked!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now