June 5, 2003 at 1:46 pm
Can any one help me with this little problem. I have a datetime field in a database. it's a datetime function, but I only care about the time. I want to find all records where is within 30 mins before or after the query time or getdate() time
so something like where runtime > getdate() - 30 mins and runtime < getdate() + 30 mins
I hope that makes sense... that's the idea, I just don't exactly know how to make the comparison correctly... oh, I don't care about the date. the days are going to be way off, but the time is the necessary part.
thanks for your help!
June 5, 2003 at 2:28 pm
dateadd(mi,30,getdate()) <= field
or
dateadd(mi,-30,getdate() >= field
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
June 5, 2003 at 2:32 pm
Forgot a ) probably should have been "and" instead of "or".
dateadd(mi,30,getdate()) <= field
and
dateadd(mi,-30,getdate()) >= field
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
June 5, 2003 at 2:42 pm
Greg,
Thanks for the help. that doesn't seem to work... from looking at the sql, it would seem that your example relies on the field having the same date as getdate(). I need to have it where the dates can be completely different, but the times are within 30 mins on either side of the current time.. Hope that makes sense...
thanks again for the help.
June 5, 2003 at 3:08 pm
ok try something like this:
create table t(d datetime)
insert into t values('2002-01-01 14:15:02')
insert into t values('2002-01-01 15:15:02')
select * from t where
abs(datediff(mi,cast('1900-01-01 '+substring(convert(char(19),d,120),12,19) as datetime),
cast('1900-01-01 '+substring(convert(char(19),getdate(),120),12,19) as datetime))) <=30
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
June 5, 2003 at 3:50 pm
greg,
thanks for the help. that got me headed in the right direction.... I think this query will work for me, although it's a bit shorter of a time span.
select * from buz_scheduled_task
where datepart(hh,runtime) >= datepart(hh,dateadd(mi,-30,getdate())) AND
datepart(hh,runtime) <= datepart(hh,getdate()) AND
datepart(mi,runtime) >= datepart(mi,dateadd(mi,-30,getdate())) AND
datepart(mi,runtime) <= datepart(mi,getdate())
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply