date less x days in SQL[resolved]

[thebloke]

Guys,

I'm sure this is straightforward enough but my brain hurts at the moment.

I want to run a query that gives me data for the last 7 days/month or whatever.

So I need to do something like:

select * from tblcounterv3
where countdate between ('a date a month ago' and convert(varchar,getdate(),103)

It's the 'a date a month ago' that I don't know how to work out.

Hope this makes some sense!

Cheers


Peter

[Ecniv]

now-7
or whatever the equivalent to now is in whichever db you are using...

1 = 1 day (ish) so a date held as a date (number) minus 1 is yesterdays.

If you are in vb/vba use the immediates window:

?clng(cdate("24 Aug 2005"))
?cdate(clng(cdate("24 Aug 2005"))-1)

[si_the_geek]

From the Convert I guess you are using SQL Server, which supports DateAdd, eg:

between (DATEADD(month, -1, getdate())  and convert(varchar,getdate(),103)

[thebloke]

Thanks chaps, very helpful as always!