sort by date and time in SQL

Hi Expert
i have a varchar column in a table, and this column hold date and time value, i wrote a simple query to sort this column descending, its working but it not seem to sort the time, i tried to convert the field to datetime data type but still it ignoring the time

SELECT  AUDIT.CurrentActor, USR.EngName, AUDIT.ReceivedDate, AUDIT.CompletedDate, AUDIT.Status
FROM tbl_ServiceAudit AUDIT  LEFT OUTER  JOIN pr1.dbo.GetUserDetailsE USR ON USR.PERUserName = AUDIT.CurrentActor 
WHERE AUDIT.ReferenceNo =123291
GROUP BY AUDIT.CurrentActor, USR.EngName, AUDIT.ReceivedDate, AUDIT.CompletedDate, AUDIT.Status
ORDER BY
CAST(AUDIT.CompletedDate AS date)
DESC

Open in new window


any suggestion
AZZA-KHAMEESAsked:
Who is Participating?
 
TheAvengerConnect With a Mentor Commented:
You need to change your cast to:

CAST(AUDIT.CompletedDate AS datetime)

Open in new window

0
 
AZZA-KHAMEESAuthor Commented:
thanks
0
 
PortletPaulCommented:
well, really, you should store datetime as datetime :)
less space and way less hassle (such as this one)
0
All Courses

From novice to tech pro — start learning today.