Datetime field using military time - need time only in standard time

Question

I have a datetime field in SQL Server 2008 which stores the date and time in military format (or international format if you prefer)

examples:

2011-02-15 10:00:00.000
2011-02-15 15:30:00.000
2011-02-15 17:30:00.000

I need to retrieve the time only portion of this in standard U.S. time format.

So

2011-02-15 10:00:00.000    needs to become 10:00 AM
2011-02-15 15:30:00.000    needs to become 3:30 PM
2011-02-15 17:30:00.000    needs to become 5:30 PM

I am looking for the best way to accomplish this in T-SQL please.

Answer 1

One way is:

Convert to varchar, which will give you:

Select CONVERT(varchar, @dt, 100) -- where @dt is your date time

Feb 15 2011 3:30PM

Subsequently, to remove the date, and get 7 chars from the right, which is the most, and TRIM is added just for extra safety, but probably isn't needed.

Select LTRIM(RIGHT(CONVERT(varchar, @dt, 100),7))

Will give you 3:30PM

Side Note: Denali makes this easier, no more magic numbers

Answer 2

As you requested this in T-SQL, you might want to look at the CAST and CONVERT syntax which specifically lists various date and time formats.

For example:

select convert(varchar, getdate(), 100)

Would give you:

Feb 3 2012 3:26PM