date part function in sql server

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

select RIGHT( convert(varchar(30), GDate, 100 ), 2 ) from tablename

select case
          when datepart( hour, getdate()) < 12 then 'It''s AM'
          else 'It''s PM'
       end

SELECT
GETDATE() AS CurrentDate,
RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) AS CurrentTime,
CONVERT(VARCHAR(10), GETDATE(), 101) + '' + RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) AS CurrentDateTime

Another Way--------------------------------------------------------------------
declare @result varchar(10)
declare @Format int
select @Format= datepart(hh,convert(varchar,CreatedOn,108))  from CandidateDetails where CandidateID = 5
if(@Format<=12)
begin
set @result='AM'
select @result as Result
end
else
begin
set @result='PM'
select @result as Result
end