greater than 7 days ago

[Sid3335]

i'm trying to run a query that gets all records where the date is in the last week.

my dates in my database are in the format dd/mm/yyyy.

when i run something like:


SELECT
`statistics`.`date`
FROM
`statistics`
WHERE
`statistics`.`date` >= 20/04/2006


it returns ALL the records, alternatively if i put quotes round the date it returns dats where the day (dd) are greater than 20. so i get the 20th onwards for ALL months.

can anyone shed any light?

[chazzy]

my dates in my database are in the format dd/mm/yyyy.


That's probably your first problem. If the data type is varchar, you can't do something like this. IF the data type is a date/datetime it'll work.

[audux]

try this with your where statement

if the field type is already datetime you may use
date <= getdate() - 7

if the field is varchar apply conversions
date <= convert(datetime, @yourdate, 101) - 7

hope it fit your needs!

[beginnerz]

hi, if i have a datetime field type, and i will like to select the date which is more than 7 days?

[audux]

i think it would be better if you perform your operations outside your query,

include the result on your query instead.

[eralper]

Hi,

The below syntax should work for any type of datetime, unless you keep it in a datetime column

select ColumnName from TableName where datediff(d, ColumnName, getdate()) < 7

Eralper
http://www.kodyaz.com