DATE_SUB PROBLEM

[QatQat]

Hi everyone

I have got a table

Create table jobs (
JobID int not null primary key auto_increment,
Date datetime,
UnitDesc varchar (50),
MakeID smallint unsigned,
etc...
)

I am trying to select all jobs booked in more than 5 days ago


select jobID, Date, UnitDesc, MakeID from jobs where date_sub(now(), INTERVAL Date DAY)>= 5;


It does not display any record although the table has many records that match that criteria, Why?


Bye

Qatqat
The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[NameAlerts]

Hi,

The root cause of your problem might be that you shouldn't call a column "Date" as it's the name of a MySQL function. Try changing the name to something else and see if that alleviates the problem.

Best wishes,
Simon.

http://www.NameAlerts.com

Domain Name Back Ordering & Monitoring
$8.95 Full Service Domain Registrations

 

[swampBoogie]

select jobID, Date, UnitDesc, MakeID
from jobs
where date <= date_sub(current_date, INTERVAL 5 DAY);

 

[QatQat]

Tryed to chaneg Date to somethign else with no results.

Swampboogie, thanks for the help but I know that one works; I instead would like to subtract my field 'Date' from now().

That's why I tought about

date_sub(now(), interval Date Day)

but I think that Date passes a complete datetime value (0000-00-00 00:00:00) and therefore Mysql does not subtract any Day from now().


Bye


Qatqat The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)