Convert varchar to 12 hour time format

A column stores the time as varchar 24 hour format. Is there a way to
change the data using the convert function to 12 hour format from the
sql select statement? Thanks.>A column stores the time as varchar 24 hour format.
Why are you storing time as a varchar? Wouldn't datetime make more sense?
>A column stores the time as varchar 24 hour format. Is there a way to
> change the data using the convert function to 12 hour format from the
> sql select statement?
SELECT [12 hour format] = LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
CONVERT(DATETIME, varchar_column), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
CONVERT(DATETIME, varchar_column), 22), 3))
FROM table
WHERE ISDATE(varchar_column) = 1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006