Hi all, I'm having a problem organising content by date for a database driven website, I'm using the now() function to enter the data into a date field so I get all of the date in one field like this: 09/08/06 11:58:44. I can't seem to organise content by date for example what SQL would I need to display content for:

• All of july
• for last week
• for this day one year ago
• for between the 17-21 of august

Is the above possible with the date all in one field or would I have to break it up into separate fields? I,m sure that I'm just doing something really dumb so apologies in advance.

I moved this to the DB forum, as I see it more related to this field.

Assuming that your DB is mysql, look at this page for a list of the date/time related functions in mysql 5+
http://dev.mysql.com/doc/refman/5.1/...functions.html
All the queries below are based on a table named tableX with a datetime or timestamp type field named "dtmY".

All of july:

select * from tableX where date(dtmY) between '2009-07-01' and '2009-07-31'

or

select * from tableX where extract(year of dtmY)=2009 and extract(month from dtmY)=7

for last week (of a month):

select * from tableX where yearweek(dtmY) = yearweek('2009-08-01' - interval 1 day)

Here, we work with the week nbr for a given month.
This should get every rows of the last week of july 2009 (starting from august 01 minus 1 day)

For last week (today - 1 week):
Deriving last query

select * from tableX  where yearweek(dtmY) = yearweek(now()- interval weekday(now()-interval 1 day) day)

for this day one year ago

select * from tableX where date(dtmY)=now()-interval 1 year

for between the 17-21 of august;

select * from tableX where date(dtmY) between '2009-08-17' and '2009-08-21'

Ahh that's great, I'm using access 2000 so had to look up some date time functions but I'm getting the hang of it. It's quite a bit more complicated than I had first thought but I can pretty much organise my data into any time period I want. Thanks for the help.