simple way to convert '1900-01-01 17:12:00.000' to 5:12pm?

[eddiekwang]

I have a table called class_time. and the column name is start_time.
datatype is datetime.

id start_time
2 1900-01-01 15:00:00.000
4 1900-01-01 17:12:00.000

i want to the convert the start_time column into a format that only
has the hour and minutes plus AM/PM ( for example, 3:00 pm, 5:12pm)

id start_time new format
2 1900-01-01 15:00:00.000 3:00 pm

is there any simple one step way to do that?

(the only way i know is to use the following query, then do a substring
where the space is. but that's too complicated)
select CONVERT(CHAR(20), start_time, 22) from class_time

Thank you in advance for your time.

[Hugo Kornelis]

On 25 Oct 2006 10:41:21 -0700, ed********@hotmail.com wrote:

>I have a table called class_time. and the column name is start_time.
datatype is datetime.

id start_time
2 1900-01-01 15:00:00.000
4 1900-01-01 17:12:00.000

i want to the convert the start_time column into a format that only
has the hour and minutes plus AM/PM ( for example, 3:00 pm, 5:12pm)

id start_time new format
2 1900-01-01 15:00:00.000 3:00 pm

is there any simple one step way to do that?

(the only way i know is to use the following query, then do a substring
where the space is. but that's too complicated)
select CONVERT(CHAR(20), start_time, 22) from class_time

Thank you in advance for your time.

Hi eddiekwang,

Something like this?

SELECT STUFF(CONVERT(char(19), start_time, 100), 1, 12, '')
FROM class_time;

--
Hugo Kornelis, SQL Server MVP

[eddiekwang]

Hi Hugo,
It worked! Thank you so much for the quick response!!. That's a very
elegant way to solve my problem.

Edy

Hugo Kornelis wrote:

On 25 Oct 2006 10:41:21 -0700, ed********@hotmail.com wrote:

I have a table called class_time. and the column name is start_time.
datatype is datetime.

id start_time
2 1900-01-01 15:00:00.000
4 1900-01-01 17:12:00.000

i want to the convert the start_time column into a format that only
has the hour and minutes plus AM/PM ( for example, 3:00 pm, 5:12pm)

id start_time new format
2 1900-01-01 15:00:00.000 3:00 pm

is there any simple one step way to do that?

(the only way i know is to use the following query, then do a substring
where the space is. but that's too complicated)
select CONVERT(CHAR(20), start_time, 22) from class_time

Thank you in advance for your time.

Hi eddiekwang,

Something like this?

SELECT STUFF(CONVERT(char(19), start_time, 100), 1, 12, '')
FROM class_time;

--
Hugo Kornelis, SQL Server MVP