date format and sorting

Resolved

(@ghuerren)

Hello. I have a query like bellow and i would like to have the output collum (AS prazo) like d-m-Y.
Every time i query a date custom field the output is like: m-d-Y, but i want d-m-Y…
Tx

SELECT
 wp_posts.post_title AS 'Ocorrência',

   GROUP_CONCAT(if(meta_key = 'ocorrencias_prazo', date_format(meta_value, '%d/%m/%Y'), NULL)) AS 'Prazo',
   GROUP_CONCAT(if(meta_key = 'ocorrencias_prazo', meta_value, NULL)) AS 'Prazo2',
date_format(post_date, '%d/%m/%Y') AS 'Data do Post'
FROM wp_postmeta
    left JOIN wp_posts
        ON wp_postmeta.post_id = wp_posts.ID
    left JOIN wp_users
       ON wp_posts.post_author = wp_users.ID
where wp_posts.post_type = 'ocorrencia' AND wp_posts.post_title LIKE 'TWR%'
 AND wp_posts.post_status = 'publish'
GROUP BY post_id
order by post_date ASC
;

[Moderator Note: Please post code & markup between backticks or use the code button. Your posted code may now have been permanently damaged by the forum’s parser.]

http://wordpress.org/plugins/custom-content-type-manager/

Viewing 1 replies (of 1 total)

Plugin Contributor fireproofsocks
(@fireproofsocks)

10 years, 6 months ago

You should always store your dates in Y-m-d format because that’s the only way you can sort them properly. You’re running into advanced problems here because you’re issuing a SQL query but your custom fields are in fact TEXT fields (that’s how the postsmeta table is defined). So you would have to cast your values as date values, which is a pain. Usually it’s easier to let MySQL sort the dates as strings (thus my recommendation to store dates in Y-m-d format).

Once you have dates being SORTED correctly, then it’s a matter of FORMATTING them using php’s date function. The CCTM does have a datef output filter https://code.google.com/p/wordpress-custom-content-type-manager/wiki/datef_OutputFilter as a convenience, but often using PHP’s date function is easier.

There are 2 questions here, neither of them are particular to the CCTM, but I hope the above helps.

Viewing 1 replies (of 1 total)

The topic ‘date format and sorting’ is closed to new replies.