CODEWITHSUNDEEP
mysqldate
Rakhi
Rakhi

Reputation: 929

mysql get records between two dates with meta value

i am firing Sql Query to get records between two dates in wp_postmeta table.

i am running following query :

  SELECT * 
  FROM `wp_postmeta`
  WHERE`meta_key` LIKE 'date'
  AND `meta_value` >= '02/01/2015' 
  AND `meta_value` <  '20/10/2015'

this query should return the result which has mata value between 02/01/2015 to 20/10/2015

unfortunately it returns 01/01/2014 and all other records of 2014.

it returns wrong results. it shows the 2014 dates in result.

what am i doing wrong? i want to get record between 2 dates by meta_value

Upvotes: 0

Views: 437

Answers (1)

juergen d
juergen d

Reputation: 204904

Convert your varchar date into a real date. Then use either between

  SELECT * 
  FROM `wp_postmeta`
  WHERE`meta_key` = 'date'
  AND STR_TO_DATE(meta_value, '%d/%m/%Y') between '2015-01-02' AND '2015-10-19'

or < and >

  SELECT * 
  FROM `wp_postmeta`
  WHERE`meta_key` = 'date'
  AND STR_TO_DATE(meta_value, '%d/%m/%Y') >= '2015-01-02' 
  AND STR_TO_DATE(meta_value, '%d/%m/%Y') < '2015-10-20'

Upvotes: 4

Related Questions