DATEDIFF function

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
BrookKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
Alan_WhiteCommented:
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
Joel MillerDevOps EngineerCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

cyberkiwiCommented:
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
LCSandman8301Commented:
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
BrookKAuthor Commented:
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
LCSandman8301Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
BrookKAuthor Commented:
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
sas13Commented:
--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
cyberkiwiCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrookKAuthor Commented:
Soln worked!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.