Get am/pm from datetime in sql query

below query gives you the am/pm from table field "yourdate" and it gives in ascending order SELECT SUBSTRING(CONVERT(varchar(20), yourdate, 22), 18, 3)  AS Expr1 FROM      table ORDER BY expr1 ; SUBSTRING(CONVERT(varchar(20), yourdate, 22), 18, 3)-->it gives you a am/pm. o/p: ---- am pm am pm