CODEWITHSUNDEEP
mysqlsqlphpmyadmin
user3586888
user3586888

Reputation: 1

phpmyadmin Date Query

I have a database with fault records. I am trying to create a query to return incidents reported more than 30 days ago.

This is the code I an using:

SELECT  `IncidentNumber`,  
        `DateReported`,  
        `ReportedBy`,  
        `AffectedSystem`, 
        `EquipmentName`,  
        `SerialNumberOfAffectedPart`, 
        `Notes`,  
        `JobStatus` 
FROM    `incident_tbl` 
WHERE   `JobStatus` != 'Closed'
AND     `DateReported` < 'DATEADD(day, -30, GETDATE())'
LIMIT   0 , 100

I have read numerous posts here, and elsewhere on the internet and have no idea why this won't work.

The code simply returns all open incidents.

Any help is much appreciated.

Thanks

Upvotes: 0

Views: 625

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

 SELECT  
`IncidentNumber` ,  
`DateReported` , 
 `ReportedBy` ,  
`AffectedSystem` , 
`EquipmentName`, 
 `SerialNumberOfAffectedPart` , 
 `Notes` , 
 `JobStatus` 
FROM  `incident_tbl` 
WHERE  
`JobStatus` !=  'Closed' 
AND  `DateReported` <  DATE_SUB( NOW(), INTERVAL 30 day)
LIMIT 0 , 100

Upvotes: 1

Patrick Moore
Patrick Moore

Reputation: 13344

Get it outside of the quotes!! MySQL interprets that as actual string, and the function is never called to output the correct date range.

Use DATE_SUB for subtraction!

SELECT  `IncidentNumber` ,  `DateReported` ,  `ReportedBy` ,  `AffectedSystem` , `EquipmentName`,  `SerialNumberOfAffectedPart` ,  `Notes` ,  `JobStatus` 
FROM  `incident_tbl` 
WHERE  `JobStatus` !=  'Closed'
AND  `DateReported` < DATE_SUB( NOW(), INTERVAL 30 DAY )
LIMIT 0 , 100

Upvotes: 0

Related Questions