zaman zaman - 3 months ago 10
PHP Question

string to date time format in mysql

I have

mysql
db and I want to get the record of daily complete transactions.

Transactions which get complete successfully get a trans date time in format like
20160816114733,1.


Now I want to convert this string to valid date time format i.e
(2016-08-16 11:47:33)
in mysql and get the daily transactions from this value.

Answer

Try this:

SELECT DATE_FORMAT(20160816114733,'%Y-%m-%d %H:%i:%s');

OR you can try this:

SELECT STR_TO_DATE(20160816114733,'%Y%m%d%H%i%s')

MySQL recognizes DATE values in these formats:

  • As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.

  • As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.

  • As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.