Início / coding / Perguntas / 78945562
Accepted
John Beasley
Asked: 2024-09-04 02:00:38 +0800 CST

Varchar str_to_date valor de data e hora incorreto

  • 772

Tive problemas ao importar um CSV para minha tabela MySQL porque não consegui fazer os campos DATE ou DATETIME funcionarem. Tentei usar LOAD DATA INFILE, mas não obtive sucesso.

Então alterei todos os campos DATE ou DATETIME para VARCHAR, e todos os meus dados finalmente foram carregados.

Mas agora preciso atualizar as datas nos campos VARCHAR para mostrar apenas o formato de data como este:

yyyy-mm-dd

Então tentei executar esta consulta:

UPDATE `table` SET 
  `CreatedDate` = STR_TO_DATE(CreatedDate, '%Y-%M-%D'), 
  `LastClickDate` = STR_TO_DATE(LastClickDate, '%Y-%M-%D'), 
  `LastOpenDate` = STR_TO_DATE(LastOpenDate, '%Y-%M-%D'), 
  `LastSendDate` = STR_TO_DATE(LastSendDate, '%Y-%M-%D'), 
  `LastUnsubscribeDate` = STR_TO_DATE(LastUnsubscribeDate, '%Y-%M-%D')

Mas estou recebendo o erro abaixo:

Incorrect datetime value: '6/25/2024 5:19:22 PM' for function str_to_date

As colunas de datas da tabela do banco de dados têm uma estrutura semelhante à abaixo:

ColumnName: CreatedDate 
Type: varchar(30)
Collation: utf8mb4_general_ci
Attributes: Yes
Default: NULL

Como posso fazer a consulta acima funcionar para que as datas possam ser exibidas no formato aaaa-mm-dd?

sql

1 respostas

  1. Best Answer

    Você está misturando STR_TO_DATE()e DATE_FORMAT().

    STR_TO_DATE()analisa uma string que contém um formato de data e hora não padrão.

    DATE_FORMAT()assume que a entrada é uma data e hora MySQL formatada corretamente e gera um formato especificado, que pode não ser padrão.

    Você tem strings não padronizadas, então STR_TO_DATE()é a função correta a ser usada, mas você precisa informar qual formato a string tem atualmente.

    Exemplo:

    mysql> select str_to_date('6/25/2024 5:19:22 PM', '%m/%d/%Y %I:%i:%s %p') as proper_datetime;
+---------------------+
| proper_datetime     |
+---------------------+
| 2024-06-25 17:19:22 |
+---------------------+

    Observe que minha string de formato '%m/%d/%Y %I:%i:%s %p'corresponde ao formato atual da sua string (consulte https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-format para obter a documentação de referência dos códigos de formato).

    A saída STR_TO_DATE()é automaticamente no formato de data e hora padrão do MySQL: AAAA-MM-DD HH:MM:SS.

    Se você quiser formatá-lo sem a parte do tempo, use DATE_FORMAT()o resultado de STR_TO_DATE():

    mysql> select date_format(str_to_date('6/25/2024 5:19:22 PM', '%m/%d/%Y %I:%i:%s %p'), '%Y-%m-%d') as proper_date;
+-------------+
| proper_date |
+-------------+
| 2024-06-25  |
+-------------+

    Ou você pode convertê-lo para o DATEtipo:

    mysql> select date(str_to_date('6/25/2024 5:19:22 PM', '%m/%d/%Y %I:%i:%s %p')) as proper_date;
+-------------+
| proper_date |
+-------------+
| 2024-06-25  |
+-------------+
    • 2

relate perguntas