BrookK
asked on
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
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
I've not tested this but something like this may do what you want?
SELECT * FROM table WHERE DATEADD(hh,24,LastUsedDate ) < {fn now()}
SELECT * FROM table WHERE DATEADD(hh,24,LastUsedDate
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...
I hope this is what you are looking for...
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()
SELECT User, Max(LastUsedDate) as MaxLastUsedDate
FROM Table1
group by User
Having DATEADD(hh, 24, Max(LastUsedDate)) > getdate()
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())
ASKER
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,
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,
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
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
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
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
ASKER
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,
Thanks,
--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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Soln worked!
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