How do get time of a datetime value using T-Sql

[9654]

i have a datetime field whose value is

2002-08-22 14:00:00.000

but i just want to return 02:00 PM how can it be done.

 

[Moptop]

Hi
You could use
CONVERT ( varchar , @thedate , 8 )
This would give you hh:mm:ss
Mark

 

[9654]

Thanks mark but this gives me just 14:00:00
how do i get the PM

so that it shows 02:00 PM in this format

 

[JamesLean]

Try this:

SELECT RIGHT('0' + LTRIM(SUBSTRING(CONVERT(varchar, @dt, 100), 13, 7)), 7)

--James

 

[Moptop]

You could do:
print substring(cast(getdate() as varchar), 13, 7)

But that does,'t give you "Seconds"
-you could add DATEPART ( s, getdate()) to the string

print substring(cast(getdate() as varchar), 13, 5) + ':' +
str(DATEPART ( s, getdate()),2,0) + substring(cast(getdate() as varchar), 18, 2)

--its worth checking if this works in all time zones - I am in the UK and it works