0

In my database table one field upload_datetime is there and it is having a value like Mon, 02 Jan 2017 15:46:23 GMT. I want to get a formatted date like 2017-01-02 15:46:23. Please somebody help me with getting it in select query.

I tried with STR_TO_DATE,CAST but I am not getting any output. It's returning NULL.

Barry Michael Doyle
  • 9,333
  • 30
  • 83
  • 143
MANOJ
  • 177
  • 5
  • 14

3 Answers3

0

You can use 

DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

The NOW() returns the present datetime and you can replace it with other datetime that you want

liontass
  • 730
  • 9
  • 24
  • How you get it the data? Via php variable?Set in the upload_datetime column in your database table the TYPE to DATETIME – liontass Jan 23 '17 at 07:16
0

In my database table one field upload_datetime is there and it is having a value like Mon, 02 Jan 2017 15:46:23 GMT.

This statement might be a bit misleading, because we don't need to be particularly concerned with how a datetime is stored internally. Your exact question is to how to format that datetime in a certain way.

The DATE_FORMAT() function is one way to go here. It accepts as input a time column, and a format mask, and returns a formatted string.

SELECT DATE_FORMAT(upload_datetime, '%Y-%m-%d %H:%i:%s') AS upload_formatted
FROM yourTable
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I have run this query **select DATE_FORMAT('Mon, 02 Jan 2017 15:46:23 GMT', '%Y-%m-%d %H:%i:%s') from Dual** – krishn Patel Jan 23 '17 at 07:25
  • @krishnpatel We have zero knowledge of what type that column is, except that its name bears the term `datetime`. If he is storing his dates as text, this is another problem altogether and he should not be converting to another text format but rather to date. – Tim Biegeleisen Jan 23 '17 at 07:26
  • @krishnpatel And you don't need `from Dual` in your query, this is MySQL not Oracle. – Tim Biegeleisen Jan 23 '17 at 07:28
0

I got the answer. after doing some R&D i found the way.Thanks all for your comment.

SELECT date_format(STR_TO_DATE(concat(substr(datetime_uploaded ,6,2),"-",substr(datetime_uploaded ,9,3),"-",substr(datetime_uploaded ,13,4)),'%d-%M-%Y'),'%Y-%M')
FROM OpsMetrics

MANOJ
  • 177
  • 5
  • 14