Purge Time

[hc_loke]

All, I records that have two columns, date col and time col.

I am trying to write a SQL statement to extract the rows that fall within a range of time.
Eg current date/time + 10 hours and
current date/time - 10 hours.

Kindly advise how can I do that.
Thanks.

[MAK]

create table purgex (id int, date varchar(10), time varchar(12))

insert into purgex select 1,'10/26/2003','09:12:00'
insert into purgex select 2,'10/26/2003','01:12:00'
insert into purgex select 3,'10/25/2003','11:22:00'
insert into purgex select 4,'10/24/2003','11:12:00'
insert into purgex select 5,'10/23/2003','14:12:00'
insert into purgex select 6,'10/27/2003','11:12:00'
insert into purgex select 7,'10/28/2003','16:12:00'
insert into purgex select 8,'10/26/2003','11:12:00'
insert into purgex select 9,'10/26/2003','23:12:00'
insert into purgex select 10,'10/26/2003','00:12:00'
insert into purgex select 11,'10/26/2003','01:58:00'
insert into purgex select 12,'10/26/2003','01:52:00'



select * from purgex where convert(datetime,date+' '+time) <=dateadd(hh,10,getdate()) and
convert(datetime,date+' '+time) >=dateadd(hh,-10,getdate())

or


declare @datex datetime
set @datex = '10/26/2003 8:53:00'

select * from purgex where convert(datetime,date+' '+time) <=dateadd(hh,10,@datex) and
convert(datetime,date+' '+time) >=dateadd(hh,-10,@datex)

[hc_loke]

hello Mak,

Thanks for your reply. I will try it out. If i need to make the 10 hours dynamic.

Eg:
base on the current date/time + x hours
current date/time - x hours

where x hours can be configured.

kindly advise.

rgds
Loke

[MAK]

declare @datex datetime
set @datex = '10/26/2003 8:53:00'
declare @hrs int
set @hrs =10
select * from purgex where convert(datetime,date+' '+time) <=dateadd(hh,@hrs ,@datex) and
convert(datetime,date+' '+time) >=dateadd(hh,-@hrs,@datex)



--change @hrs value from 10 to something else

[hc_loke]

hello Mak,

Thanks for your reply. I have tested it successfully.

rgds
Loke