Convertendo varchar em date no MySQL

Publicado por Fábio Berbert de Paula em 23/07/2007

[ Hits: 52.535 ]

Blog: https://mestrefabio.com

 


Convertendo varchar em date no MySQL



Peguei uma manutenção de um sistema PHP/MySQL pra fazer e surgiu a necessidade de ordenar determinada tabela pelo campo data, porém pra minha surpresa esse campo era do tipo VARCHAR() ao invés de DATE() ou DATETIME().

A data estava armazenada na tabela no formato "dd/mm/yyyy", exemplo:

SELECT data FROM contas;
+------------+
| data       |
+------------+
| 26/11/2003 | 
| 04/12/2003 | 
| 28/11/2003 | 
| 05/12/2003 | 
| 29/12/2003 | 
+------------+

O problema é que ao mandar ordenar a tabela por data, por ser VARCHAR o resultado não funciona como o esperado:

SELECT data FROM contas ORDER BY data;
+------------+
| data       |
+------------+
| 04/12/2003 | 
| 05/12/2003 | 
| 26/11/2003 | 
| 28/11/2003 | 
| 29/12/2003 | 
+------------+

Isso acontece porque se o campo é texto, ele começa a ordenar da esquerda pra direita em ordem alfanumérica, onde 04/12 é menor que 26/11, o que está errado, visto que em data a gente precisa levar em conta, em ordem de prioridade, ano-mês-dia.

Pra resolver esse problema sem ter de mexer na tabela (o que não tinha permissão pra fazer), use a função str_to_date() do MySQL:

SELECT str_to_date(data, '%d/%m/%Y') AS data FROM contas ORDER BY data;
+------------+
| data       |
+------------+
| 2003-11-26 | 
| 2003-11-28 | 
| 2003-12-04 | 
| 2003-12-05 | 
| 2003-12-29 | 
+------------+

Hmmm, agora sim! A sintaxe da função é:

str_to_date(CAMPO, 'formato armazenado da string')

Maiores informações:
Outras dicas deste autor

Comando find: expressões regulares e exclusão de diretórios

Site de depuração e aprendizado de expressões regulares

Como fazer o editor Vim mostrar/ocultar os números das linhas

Jogando Gamão no GNU/Linux e no FIBS

Como descobrir a velocidade de sua placa de rede via linha de comando

Leitura recomendada

MySQL Error: #1558 - Column count of mysql.proc is wrong. Expected 20, found 16. ... Please use mysql_upgrade to fix this error

SELECT data = hoje/ontem/anteontem no MySQL

MySQL - Vídeo mini-curso de introdução

Access denied for user 'root'@'localhost' no MySQL Server Community 5.7 [Resolvido] - CentOS7 x86_64

Usando transações com MySQL

  

Comentários
[1] Comentário enviado por hardwarez em 20/03/2008 - 12:25h

Boa Fábio!!! me serviu aki para converter um banco de dados que importei (DBF) q veio com as datas todas zuadas

UPDATE tabela set data=str_to_date(data, '%d.%m.%Y')

a data estava assim: 01.01.2001

[2] Comentário enviado por fabianoplanura em 06/02/2016 - 18:27h

Pessoal, sou novo aqui.

Estou precisando da ajuda de vocês e desde já agradeço a quem pudem dar alguma dica.
Gostaria de saber como faço para usar uma variável para busca com essa função: SELECT str_to_date(data, '%d/%m/%Y') AS data FROM contas ORDER BY data;

Segue meu codigo:

$query_rs_licitacoes = "SELECT * FROM tb_licitacoes WHERE tb_licitacoes.descricao LIKE '%$busca%' OR tb_licitacoes.data = '$busca' OR tb_licitacoes.modalidade = '$busca' ORDER BY data DESC";

Desse jeito não está ficando em ordem decrescente e tentei de várias maneiras inserir o Select str_to_date aí junto e não deu certo.

Para mostrar somente a coluna data dá certo, mas preciso que exiba as outras também. Alguém tem alguma idéia.
Depois pretendo usar um echo transDate para inverter a data para ficar ddmmyyyy.



Contribuir com comentário




Patrocínio

Site hospedado pelo provedor RedeHost.
Linux banner

Destaques

Artigos

Dicas

Tópicos

Top 10 do mês

Scripts