CODEWITHSUNDEEP
sqldatefilter
Marvin
Marvin

Reputation: 513

SQL date Statement

I need some help figuring out and SQL Statement.

I know what I want I just cant express it.

Im using php, so it doesnt need to be exclusivly SQL, its to act as a filter.

Pseudo code

$query="SELECT * FROM MyTable WHERE 'TIS'  is not older than 2 days or empty  = ''$ORDER"; }

TIS in the name of the column in my table were I store dates in this format 03-12-09 (d,m,y). The $ORDER is for ordering the values based on values from other fields not dates.

Im looking at 

SELECT * 
FROM orders
WHERE day_of_order >
(SELECT DATEADD(day,-30, (SELECT MAX(day_of_order) FROM orders)) AS "-30 Days");

But i dont quite think im on the rigth track with this.

Thanks

Upvotes: 0

Views: 1704

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

Try the following:

SELECT *
  FROM MyTable
  WHERE COALESCE(TIS, SYSDATE) > SYSDATE - INTERVAL '2' DAY
  $ORDER

I don't know what database you're using - the above uses Oracle's method of dealing with time intervals. If you're using SQL Server the following should be close:

SELECT *
  FROM MyTable
  WHERE COALESCE(TIS, GETDATE()) > DATEADD(Day, -2, GETDATE())
  $ORDER

In MySQL try this:

SELECT *
  FROM MyTable
  WHERE COALESCE(TIS, NOW()) > DATE_SUB(NOW(), INTERVAL 2 DAYS)
  $ORDER

I hope this helps.

Upvotes: 3

Marvin
Marvin

Reputation: 513

So, I was pretty lost in all this.

How did it got solved:

  • First I understood that the Statement I was using was not supported by MySql thanks to eligthment from Bob Jarvis.

_ Second In a comment by vincebowdren wich "strongly" adviced me to change the data type on that field to Date wich indeed I had not, it was a string.

It was pretty Dumb for me to try using SQL operations for Dates on a field that had String values.

So I just RTFM: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

and:

mysql> SELECT something FROM tbl_name -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

Then proceeded to change the field value to date.

and this is my perfectly working query:

$query="SELECT * FROM MyTable WHERE DATE_SUB(CURDATE(),INTERVAL 2 DAY) <= TIS OR TIS = 0000-00-00 $ORDER "; }

I would like to thank the posters for their aid.

Upvotes: 0

Related Questions