0

I've read multiple posts and find nothing wrong with my syntax, can someone point out the error?

I am testing some queries in PHP MyAdmin, on a WordPress Database. The table I am querying has a meta_key of "Listing-End-Date" and meta_values of "2018/06/30".

My query looks like this: 

SELECT * FROM `table`
WHERE `meta_key` LIKE 'Listing-End-Date'
AND STR_TO_DATE('meta_value', '%Y/%m/%d') > CURDATE()

and it returns 0 results. To test my STR_TO_DATE format, I did a new query:

SELECT `meta_key` , STR_TO_DATE( 'meta_value', '%Y/%m/%d' )
FROM 'table'
WHERE `meta_key` LIKE 'Listing-End-Date'

I get the expected 1 result returned, but the date is NULL. Is it because I am using PHP My Admin or did I type something wrong?

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
Lisa Baird
  • 33
  • 7

3 Answers3

0

Meta_value should be ticked and not quoted inside string_to_date

Bleach
  • 561
  • 4
  • 11
  • Thanks!I knew it had to be something painfully obvious. Removing the apostrophes (is that the same as a tick?) solved the problem. It seems that with all the pages and website and examples I see, they always omit something that must be obvious to everyone else – Lisa Baird Jun 25 '18 at 13:57
  • It will work without the ticks as you have found. But ticks are around meta_key in your above select. See the difference between those and the single quotes around listing_end_date? – Bleach Jun 25 '18 at 15:33
0

You were trying to convert string value 'meta_value' to date which obviously fails. You probably wanted to reference a column meta_value from table meta_key instead:

SELECT * FROM `table`
WHERE meta_key LIKE 'Listing-End-Date'
AND STR_TO_DATE(meta_value, '%Y/%m/%d') > CURDATE()

I've removed backticks as they're not required here (all but table). Though, you really shouldn't name your table with a reserved keyword even if you can using backticks.

Also, one thing to note would be that meta_key LIKE 'Listening-End-Date' is equivalent of equality comparison meta_key = 'Listening-End-Date' because you don't do partial search (you're not using % anywhere).

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
  • Thanks, indeed it was the apostrophes (backticks?). FYI, my table is not named table, but I didn't want to share its actual name. As for using "LIKE," it was my understanding that LIKE is case in-sensitive and whereas an equal sign (=) is an exact match. I use LIKE mostly due to my own fat fingers...I hate chasing down upper/lower case bugs. You would think that by now I'd just put everything in lower case. – Lisa Baird Jun 25 '18 at 14:01
0

Try to use DATE_FORMAT()

check this page

Fathi
  • 138
  • 2
  • 2
  • 7