How to sort a date that is in varchar format?

Question

How to sort a date that is in varchar format?

Navigation

#1 by (5 votes)

1

I have the following select:

    SELECT a.nome, a.cidade, a.telefone, a.dt_criado
      FROM cadastro AS a
  GROUP BY a.nome, a.cidade, a.telefone, a.dt_criado 
    HAVING COUNT(*) > 0 
     ORDER BY a.dt_criado 
      DESC

a.dt_create is a varchar, but does not sort correctly.

Select result:

02/05/2018 - 15:38:21 02/05/2018 - 15:39:48 02/02/2018 - 15:40:05 02/05/2018 - 15:44:19 02/05/2018 - 17:41:42 03/05/2018 - 08:53:06 03/05/2018 - 08:53:06 03/05/2018 - 08:53:06 03/05/2018 - 08:53:06 03/05/2018 - 08:53:06 03/05/2018 - 08:53:06 03/05/2018 - 14:11:29 03/05/2018 - 14:12:04 03/05/2018 - 14:13:06 03/05/2018 - 14:48:59 04/20/2018 - 09:35:59 20/04/2018 - 12:38:13 04/20/2018 - 16:49:15 04/20/2018 - 16:49:31 20/04/2018 - 16:55:23 04/20/2018 - 16:55:23 04/20/2018 - 16:55:23 -

sql sql-server

asked by anonymous 03.05.2018 / 21:43

1 answer

How to leave a pie div? [duplicate] check if only one field is the same as the input field

score 5 · Answer 1

Direct casting using CONVERT(DATETIME, _a_sua_data_, 103) is not working because of the '-' that separates the date and time. I think this is not a standard SQL Server format.

One possible solution is to remove the '-' and then do the conversion.

CREATE TABLE Testes
(
  coluna_data VARCHAR(50)
)

INSERT INTO Testes(coluna_data) VALUES
('02/03/2018 - 22:45:01'), 
('02/05/2018 - 12:56:03'), 
('02/05/2018 - 14:22:03'), 
('02/05/2018 - 19:39:22'), 
('02/05/2018 - 12:46:45'), 
('03/06/2018 - 06:34:52'), 
('03/04/2018 - 02:35:09')

SELECT coluna_data 
  FROM Testes
 ORDER BY CONVERT(DATETIME, REPLACE(coluna_data, '-', ''), 103)

The result is expected

coluna_data

02/03/2018 - 22:45:01 
03/04/2018 - 02:35:09 
02/05/2018 - 12:46:45 
02/05/2018 - 12:56:03 
02/05/2018 - 14:22:03 
02/05/2018 - 19:39:22 
03/06/2018 - 06:34:52