Get am/pm from datetime in sql query

Get time format of datetime in sql.

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