[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

DATEDIFF function

Posted on 2010-08-19
13
Medium Priority
?
721 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
Independent Software Vendors: 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!

 
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 750 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

656 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