Select dates search

[nikko50]

Hello.
How can I search dates in mysql??
Let's say I have a date=2004-09-14, I would like to take that date -1 week. Is there a function that subtracts or adds a day, week, month whatever??
Tracy

[TVBanks98]

MySQL ??

As "they" say......RT*M

It should be the same in most SQL DBs.

Enjoy,
T

[nikko50]

Hey T!! Below is what I have so far. Where do I specify all records between these dates. I'm trying to get all records 1 week before current date up to 31 days after current date.

//SELECT *
//FROM customers
//WHERE DATE_SUB( 'CURDATE()', INTERVAL 7
//DAY )
//BETWEEN DATE_ADD( 'CURDATE()', INTERVAL 31
//DAY )

[TVBanks98]

Nikko,

You want to do something like this:

SELECT *
FROM customers
WHERE date_column_name BETWEEN
DATE_SUB( 'CURDATE()', INTERVAL 7
DAY )
AND DATE_ADD( 'CURDATE()', INTERVAL 31
DAY )

Good Luck......

[nikko50]

Tried..

SELECT *
FROM customers
WHERE orderdate BETWEEN
DATE_SUB( 'CURDATE()', INTERVAL 7
DAY )
AND DATE_ADD( 'CURDATE()', INTERVAL 31
DAY )

Query completed successfully but no records were returned. I have rows that have orderdate 7 days before today through all orders 31 days from today. Any suggestions T?

[TVBanks98]

I suspect the format of the date returned from the column function does not match the format of the date in the database.

I'm not familiar with MySQL enough to know all the data formats

Read the manual on date functions and data formats and see if they're in the same format.

Not sure other than that.

[nikko50]

OK. Will check it out. Thanks for all the help🙂