phpmyadmin Date Query

Question

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

Abhik Chakraborty · Accepted Answer · 2014-04-29 20:34:50Z

1

 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

answered Apr 29, 2014 at 20:34

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick Moore Over a year ago

This is a duplication of my earlier answer, with no explanation. Why did it get the upvote and not my answer?

Patrick Moore · Accepted Answer · 2014-04-29 20:33:04Z

0

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

answered Apr 29, 2014 at 20:33

Patrick Moore

13.4k5 gold badges41 silver badges63 bronze badges

1 Comment

user3586888 Over a year ago

Perfect! Thanks very much. Apologies for the simplicity, I'm somewhat of a novice!

Collectives™ on Stack Overflow

phpmyadmin Date Query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related