Sort datetime varchar

Question

I'm looking for some help. I have a date field as datetime YYYY/MM/DD HH:MM:SS and I need it to be a DD/MM/YYYY HH:MM:SS. Then I used convert(varchar) but can't order by desc, show this:

31/01/2019 17:00:00:000 31/01/2019 17:00:00:000 18/01/2019 13:30:00:000 18/01/2019 13:30:00:000 07/02/2019 03:00:00:000 07/02/2019 03:00:00:000 14/02/2019 12:00:00:000 CONVERT(VARCHAR(10),ISNULL(tbl_date1,tbl_date2),103) + ' ' + CONVERT(VARCHAR(20),ISNULL(tbl_date1,tbl_date2),14)

I dont know how to do this in sql. I have tired cast, and convert, and I keep getting same results.

I have been searching, and trying different methods, just not getting results. Any help would be so appreicated, thank you!

You can sort by the DATETIME value while returning the formatted VARCHAR value. — Ross Bush
– Ross Bush, Commented Jul 3, 2019 at 12:39
You do not have to order by the varchar column, you can create the varchar column as output and still order by the original datetime column. I assume that is what is wrong but not sure because I do not see the actual query. — Igor
– Igor, Commented Jul 3, 2019 at 12:48
Yes, I tried this but I get the same problem as before: ORDER BY items must appear in the select list if SELECT DISTINCT is specified — BRKZ
– BRKZ, Commented Jul 3, 2019 at 12:56
Again, you need to include the actual full query that is producing that error message. That last line of code is not a query that would produce that error message. — Igor
– Igor, Commented Jul 3, 2019 at 12:58

gotqn · Accepted Answer · 2019-07-03 12:51:18Z

1

Try this:

SELECT * ,CONVERT(VARCHAR(10),ISNULL(tbl_date1,tbl_date2),103) + ' ' + CONVERT(VARCHAR(20),ISNULL(tbl_date1,tbl_date2),14) FROM ... ORDER BY ISNULL(tbl_date1,tbl_date2) DESC

answered Jul 3, 2019 at 12:51

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gotqn Over a year ago

@BRKZ just add ISNULL(tbl_date1,tbl_date2) to your SELECT query - for example as last column.

BRKZ Over a year ago

It's a good way. But this report It will be to download to excel and run macros automatically. Has another way to fix this?

gotqn Over a year ago

@BRKZ Then, no way to exclude the column from the list when DISTINCT is used.

Collectives™ on Stack Overflow

Sort datetime varchar

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related