Date Question

I want to select all records where the date is less than today minus 30. I have tried (Current_Date - 30) but the syntax is wrong. any ideas?

Maybe something like this:

declare @.whatTime table ( aTime datetime )
insert into @.whatTime values ('1/1/7')
insert into @.whatTime select dateadd (mi, -12, getdate())

select * from @.whatTime
where aTime >= getdate() - cast ('0:30:00.00' as datetime)

-- aTime
--
-- 2007-03-08 15:12:45.920

Another alternative would be something like:

select * frm @.whatTime
where aTIme >= dateadd (mi, -30, getdate())

|||

It wasn't clear from your original post exactly what time/date units the '30' (that you want to subtract) should be in.

Kent provided examples that will subtract 30 minutes, for a wider range of units for use in his DATEADD example then see the following - quoted from BOL:

DATEADD (datepart , number, date )

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Chris

|||

Oh, brother! You are SOO right! I think the question had to do with days and not minutes. :-) ( Still laughing at myself! )

If it is days try:

getdate() - 30

That should work fine.

|||I am laughing too!!! I was referring to days, and the gedate works fine. Thanks|||

Chris:

I so appreciate your answer. I had bricked this so badly and you did such a good job of re-directing AND leaving me an easy out. I really admire the work with words.

Kent

|||

No worries. :) Your answers were fundamentally correct - I also interpreted the requirement as being for 30 minutes when I first read the original post.

Thanks for the info over GETDATE() - x, I didn't realise you could do that...

Cheers
Chris