0

I've a question. I want to display all records where "createdat"(column name) is older than 2 weeks.

Normally this is an easy one. The unfortunate problem is, all my records are inserted into the database with the time/date format as followed: 01-01-2020 instead of 2020/01/01.

I was fiddling around with this one for quite a while. Can you guys please help me?

SELECT DATE_FORMAT(createdat, '%d-%m-%Y'), status 
FROM facturen 
WHERE status='Niet voldaan' AND createdat > DATE_ADD(NOW(), INTERVAL - 14 DAY)
Shadow
  • 33,525
  • 10
  • 51
  • 64
Tim Nijland
  • 65
  • 1
  • 8
  • 2
    What is the datatype of `createdat`? – Barmar Oct 26 '20 at 19:18
  • 1
    And what exactly is the problem? You didn't actually explain a specific issue with the query. – ADyson Oct 26 '20 at 19:19
  • @barmar its just a VARCHAR – Tim Nijland Oct 26 '20 at 19:20
  • 1
    Could you specify an alias using "as" for your formatted `creatdat` column, and use that for the comparison? – droopsnoot Oct 26 '20 at 19:21
  • 3
    you'll need to cast it to a datetime then before you can compare it to other dates. But **please** convert that field to a datetime. There's a reason that data type exists - so use it. – ADyson Oct 26 '20 at 19:21
  • 1
    You'll need [STR_TO_DATE](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date) – aynber Oct 26 '20 at 19:21
  • @ADyson Since i use the dutch date format (Which I put as a VARCHAR into my DB) the string isn't taking the "-14 days" in count. It just shows them all. It doesn't filter. – Tim Nijland Oct 26 '20 at 19:22
  • U guys, are all awesome! Fixed it! Thanks! – Tim Nijland Oct 26 '20 at 19:27
  • "Which I put as a VARCHAR into my DB"...that's ultimately the root cause of this issue. So STR_TO_DATE will cover up the problem for now, and I'm glad you've managed to get that working, but ultimately you need to sort out your column's data type. Then you won't need to keep converting (which isn't very efficient) when you want to do comparisons, or sorting, etc. – ADyson Oct 26 '20 at 20:03

1 Answers1

0

You need to use STR_TO_DATE() to parse the date. You don't need to use DATE_FORMAT(), since it's already in that format.

You also should use < to get records older than a time, not >.

SELECT createdat, status
FROM facturen
WHERE status = 'Niet voldaan'
AND STR_TO_DATE(createdat, '%d-%m-%Y') < DATE_SUB(NOW(), INTERVAL 2 WEEK)
Barmar
  • 741,623
  • 53
  • 500
  • 612