SQL Question

sorting by DATE string MSSql

Sorting by date gives wrong output. The date that is being entered, is entered and stored as a string or rather Varchar(50), so when i sort it only takes the first part of example "22-11-2016" so "22". Too clarify: this is what my out is:

enter image description here

with the following query string:

SELECT TOP 1 CHECKEDIN, DATE
FROM timereg
WHERE UNILOGIN = @unilogin
ORDER BY DATE DESC, TIME DESC


and clearly column 9 should be on top because it is later that month.
how would i convert this somehow to datetime and sort it so it actually works, or just sort after the whole string.

-thanks

Answer

Convert your varchar to an actual date

... ORDER BY convert(date,DATE,103) DESC 
Comments