How to sort a date that is in varchar format?

Asked

Viewed 660 times

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_created is a varchar, but does not order correctly.

Select result:

02/05/2018 - 15:38:21 02/05/2018 - 15:39:48 02/05/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 20/04/2018 - 09:35:59 20/04/2018 - 12:38:13 20/04/2018 - 16:49:15 20/04/2018 - 16:49:31 20/04/2018 - 16:55:23 20/04/2018 - 16:55:23 20/04/2018 - 16:55:23 -

sql sql-server

  • Could you post a small sample of the content? And how it looks after order by?

  • dt_created 02/05/2018 - 15:38:21 02/05/2018 - 15:39:48 02/05/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 20/04/2018 - 09:35:59 20/04/2018 - 12:38:13 20/04/2018 - 16:49:15 20/04/2018 - 16:49:31 20/04/2018 - 16:55:23 20/04/2018 -2018 - 16:55:23 04/20/2018 - 16:55:23

  • exchange for order by Convert(datetime,a.dt_criado,103) desc

  • Conversion failed when Converting date and/or time from Character string.

  • 1

    I made a change, it’s datetime instead of date

  • now gave: The Conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

  • There is some date your incomplete. I will post a Fiddle for you to see!

  • Take a look here: http://sqlfiddle.com/#! 18/ca821/1

  • I had a similar problem in Oracle by a legacy table with a "date" field that is a string , I did the following a Function converts to string to date , but in a protected block treating conversion error and returning null in this case, is not the best solution but it was the possible.

  • apparently by sqlfiddle everything normal....

  • maybe if ordering just by day/month/year would help even if it doesn’t take the time...has how to make a substring will be?

Show 6 more comments

1 answer

5

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

One possible solution is to remove the '-' and after this perform 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 as 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

See also the fiddle

  • 1

    On the fly! Thank you very much worked perfectly

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.