Solved

DATEDIFF function

Posted on 2010-08-19
13
716 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
[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
  • 3
  • 2
  • +4
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33474684
>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
ID: 33474714
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
ID: 33474716
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33474774
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
ID: 33474777
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
ID: 33474782
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
 
LVL 6

Expert Comment

by:LCSandman8301
ID: 33474836
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33474856
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33474882
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
ID: 33474896
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
ID: 33477534
--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
ID: 33481755
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
ID: 33638739
Soln worked!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change this SQL to get all nodes 3 54
how to make geography query faster?  SQL 7 59
Database Mail Profiles 1 44
2008 to 2016 jump (SQL Server) 1 27
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

738 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