DATE_FORMAT和STR_TO_DATE不起作用。

huangapple go评论62阅读模式
英文:

DATE_FORMAT and STR_TO_DATE are not working

问题

我正在尝试获取以05/01/2023 04:33:59 AM格式化的日期中的月份或日期,但似乎这两个函数不起作用。它给我返回了NULL0的结果。

这是我尝试过的脚本。我已经将示例的查询组合在一起。

SELECT 
     format1.date1, format2.date2 
FROM (SELECT DATE_FORMAT(datetime,'%d') AS date1 FROM may2023) format1, 
      (SELECT DAY(STR_TO_DATE(datetime, '%Y-%m-%d %h:%i:%s %p')) AS date2 FROM `may2023`) format2

有关这个问题的任何建议和帮助?提前感谢。

英文:

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 and help for this one? Thanks in advance.

答案1

得分: 0

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 月份,
DATE_FORMAT(STR_TO_DATE(datetime, '%m/%d/%Y %h:%i:%s %p'), '%d') AS 日
FROM may2023;

英文:

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;

huangapple
  • 本文由 发表于 2023年7月11日 07:39:53
  • 转载请务必保留本文链接:https://go.coder-hub.com/76657922.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定