Dhaval P Dhaval P - 11 months ago 46
MySQL Question

selecting date in specific format from mysql

i want to get a date in yyyy-MM-dd format from mysql which has a field type timestamp. docs suggest to use Date(field_name). however i achieved the result as i wanted in phpMyAdmin. The selected biew shos Date in the format i wanted. but when i try it from sails using sails-mysql adapter its giving time and zone also.

that is when i run query SELECT Date(field) FROM mytable i get the following results.

  1. for phpMyAdmin the result was "2016-10-22"

  2. for sails query the response was "2016-10-21T18:30:00.000Z"

the value stored in table is.. "2016-10-22 12:31:39"

what is the reason behind sails and phpMyAdmin gives different responses for the same queries. is there anything else to consider?

Answer Source

you can use mysql date format function like this

 select DATE_FORMAT(your_date,'%Y-%m-%d'); 

it will five you the exact format