Solved

DATEDIFF function

Posted on 2010-08-19
13
715 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 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
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!

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 73
monitoring configuration for SQL server DB 32 45
MS SQL Server select from Sub Table 14 41
Union & Crosstab qrys 101! 6 53
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

685 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