Displaying time from Datetime datatype

Sreenivas

Hi every one,
I am new to sql server2005,I have datetime column
in my database ..I need to display only time part of the datetime .I
need to display in AM/PM format ..datetime is stored in this format mm/
dd/yyyy hh:mm:ss AM/PM .I need to display in this format hh:mm:ss AM/
PM ,How can i do that?? I searched for this on net but i got 24hour
format .
i tried this on my own ,but no hope!

Thanks in advance,

Oct 31 '08 #1

Plamen Ratchev

The DATETIME data type does not have format but it is rather stored as
binary value. Formatting is normally best done on the client side.

In T-SQL you can use the CONVERT function with style 109 and a bunch of
string manipulations to get the time formatted as string:

SELECT RIGHT('0' +
LTRIM(STUFF(RIGHT(
CONVERT(CHAR(26), CURRENT_TIMESTAMP, 109)
, 14),
9, 4, ' ')),
11);

--
Plamen Ratchev
http://www.SQLStudio.com

Oct 31 '08 #2

Sreenivas

On Oct 31, 10:39*am, Plamen Ratchev <Pla...@SQLStudio.comwrote:

The DATETIME data type does not have format but it is rather stored as
binary value. Formatting is normally best done on the client side.

In T-SQL you can use the CONVERT function with style 109 and a bunch of
string manipulations to get the time formatted as string:

SELECT RIGHT('0' +
* * * * LTRIM(STUFF(RIGHT(
* * * * CONVERT(CHAR(26), CURRENT_TIMESTAMP, 109)
* * * * * * * * * * * , 14),
* * * * * * * * * * 9, 4, ' ')),
* * * * * * * 11);

--
Plamen Ratchevhttp://www.SQLStudio.com

Plamen Ratchev..
Thanks a lot for your help

Oct 31 '08 #3