CODEWITHSUNDEEP
sql-servert-sql
Matt
Matt

Reputation: 1500

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

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.

Upvotes: 6

Views: 24052

Answers (3)

Oded
Oded

Reputation: 499012

DateTime is stored in an internal representation - there is no format associated. When you need to display the DateTime, specify the formatting you want for converting the DateTime into a string.

It is much better to let the application decide on formatting than formatting in SQL.

See standard and custom Date and Time Format Strings for more information.

Upvotes: 1

Sir Crispalot
Sir Crispalot

Reputation: 4844

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

Upvotes: 1

Ta01
Ta01

Reputation: 31610

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

Upvotes: 14

Related Questions