DATE_FORMAT and STR_TO_DATE are not working

July 11, 2023

I am trying to get the month or day in a date formatted in 05/01/2023 04:33:59 AM but seems like the two functions are not working. It gives me a NULL and 0 results.

This is the scripts that I’ve tried. I have combined the queries for the sample.

SELECT format1.date1, format2.date2 FROM (SELECT DATE_FORMAT(datetime,'%d') AS date1 FROM may2023) format1, (SELECT DAY(STR_TO_DATE(datetime, "%Y")) AS date2 FROM `may2023`) format2

Any advice for this one? Thanks in advance.

>Solution :

Are you looking for something like that?

CREATE TABLE may2023 (
  datetime VARCHAR(50)
);

INSERT INTO may2023 (datetime)
VALUES ('05/01/2023 04:33:59 AM');

SELECT
  DATE_FORMAT(STR_TO_DATE(datetime, '%m/%d/%Y %h:%i:%s %p'), '%m') AS month,
  DATE_FORMAT(STR_TO_DATE(datetime, '%m/%d/%Y %h:%i:%s %p'), '%d') AS day
FROM may2023;