SQL order by convert

3 Views

I have a problem with ordering a query by converting longtext (b.meta_value) into a datetime. I am getting error 1064.
the format in my database is mm/dd/yyyy and I tried to order by, but I keep getting error 1064. Where is the problem?: 

convert(convert(b.meta_value,char(30)),DATETIME, 101)
convert(DATETIME, convert(b.meta_value,varchar(30)), 101)
convert(DATETIME, STR_TO_DATE(b.meta_value, '%m/%d/%Y'), 101)

Here is my query:

SELECT 
d.name as name,
b.post_id as post_id,
b.meta_value as meta_value 
FROM wp_posts a 
inner join wp_postmeta b 
on a.id=b.post_id 
inner join wp_term_relationships c 
on c.object_id=b.post_id 
inner join wp_terms d 
on c.term_taxonomy_id=d.term_id 
where meta_key in('px_event_from_date') 
  and a.post_type='events' 
  and a.post_status!='trash' 
  and d.term_id = 104 
  and STR_TO_DATE(b.meta_value, '%m/%d/%Y') <= CURDATE() 
order by convert(DATETIME, STR_TO_DATE(b.meta_value, '%m/%d/%Y')) desc 
limit 0,1
Post Views: 3

Related posts

Leave a Reply

2 comments

  1. You should set meta_key filter in your JOIN something like meta_key = 'my_date' (because I don’t know which meta key you are trying to catch):

    SELECT 
d.name as name,
b.post_id as post_id,
b.meta_value as meta_value 
FROM wp_posts a 
inner join (
  SELECT 
     post_id,
     meta_value
  FROM wp_postmeta 
  WHERE meta_key = 'my_date'
) b 
on a.id=b.post_id 
  and  STR_TO_DATE(b.meta_value, '%m/%d/%Y') <= CURDATE() 
inner join wp_term_relationships c 
on c.object_id=b.post_id 
inner join wp_terms d 
on c.term_taxonomy_id=d.term_id 
where meta_key in('px_event_from_date') 
  and a.post_type='events' 
  and a.post_status!='trash' 
  and d.term_id = 104 
order by STR_TO_DATE(b.meta_value, '%m/%d/%Y') desc 
limit 0,1

  2. You are using convert incorrectly. You need cast instead.

    In your case, however, you need STR_TO_DATE:

    select str_to_date(b.meta_value, '%d/%m/%Y') from ...

    assume you have data value like ’02/02/2015′