Get am/pm from datetime in sql query

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