Format Time in a Query

[nadarm]

In my table in SQL 2005 it has the date and time in the field. Example: 3/7/2007 9:14:00 AM. The question I have is how do you format this field so it displays 09:14:00 AM?

[rmiao]

Can try:

convert(char(12), col_name, 108)

[nadarm]

That gives me 11:19:15 but no AM/PM

[skhanal]

131 returns AM/PM but in dd/mm/yyyy format

select convert(char(26),getdate(),131)

There are several styles supported with CONVERT function, check BOL.

[rmiao]

There is no such style in convert function, may need use substring to get what you want.

[Rajesh GGKTECH]

Hi,

SELECT Substring('3/7/2007 9:14:00 AM', (CHARINDEX(' ','3/7/2007 9:14:00 AM'))+1, (CHARINDEX(' ','3/7/2007 9:14:00 AM'))+3)

[rmiao]

The thread is almost 2 years old.

[Rajesh GGKTECH]

sorry i didnt check the date