0

How to format date on select query?

current code:

SELECT id, status, TransactionDate FROM Transaction

result Mon Mar 28 2022 00:00:00 GMT+0800 (Philippine Standard Time)

what ive done so far:

SELECT id, status, DATE_FORMAT(TransactionDate, '%m-%d-%y') FROM Transaction

result null

reference: Format date in SELECT * query

Improve this question
3
  • And what's the wanted result? Commented Mar 28, 2022 at 12:51
  • just like this 09-22-11 Commented Mar 28, 2022 at 12:51
  • this questions appears to be an orphan (user15404864 does not link to anything) Commented Apr 28, 2022 at 5:00

1 Answer 1

Reset to default
1

This answer assumes that TransactionDate is actually text. You could use STR_TO_DATE to first convert your date string to a bona fide datetime, then use DATE_FORMAT to get the output text you want.

SELECT DATE_FORMAT( STR_TO_DATE(LEFT(TransactionDate, 15), '%a %b %d %Y'), '%m-%d-%y') AS TransactionDateOut FROM `Transaction`;

Demo

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments