I'm trying to grab the date from my database in a standard timestamp and display it as ISO 8601. I'm unable to easily do it in PHP so I'm trying to do it in my SELECT statement. This is what I have, but it displays an error:
SELECT * FROM table_name ORDER BY id DESC DATE_FORMAT(date,"%Y-%m-%dT%TZ")
What am I doing wrong?
MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format.
MySQL uses yyyy-mm-dd format for storing a date value. This format is fixed and it is not possible to change it. For example, you may prefer to use mm-dd-yyyy format but you can't. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want.
Time zones in ISO 8601 are represented as local time (with the location unspecified), as UTC, or as an offset from UTC.
The DATE_FORMAT(DateColumn)
has to be in the SELECT
list:
SELECT DATE_FORMAT(date, '%Y-%m-%dT%TZ') AS date_formatted FROM table_name ORDER BY id DESC
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With