• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

Extracting and Formatting a DateTime column in SQL Server 2008

I'm using SQL Server 2008 and have a query in which I need to extract a column defined as a DateTime and put it in the following format:

11:15:45 AM
11:30:33 AM
11:45:05 AM
12:00:23 PM
12:15:48 PM    etc.

Open in new window


I know in SQL Server 2012 it has functions to do this but I have NO idea how to achieve this in 2008 version.
0
BlakeMcKenna
Asked:
BlakeMcKenna
  • 4
  • 3
1 Solution
 
Scott PletcherSenior DBACommented:
Assumed you wanted a leading zero if the time was below hour 10:

SELECT STUFF(REPLACE(RIGHT(CONVERT(varchar(30), datetime_column, 9), 14), ' ', '0'), 9, 4, ' ')
0
 
BlakeMcKennaAuthor Commented:
Scott,

I acutally figured it out. Here is what the code and the result look like (see screenshot).

If your way is a better, please let me know why!

Thanks!
screenshot.jpg
0
 
Scott PletcherSenior DBACommented:
That won't work because 8 uses 24-hour hours, i.e., you'll get "13:nn:nn PM" or "17:nn:nn PM", etc..

I prefer the method I posted above, although you could also use format 8 and subtract 12 hours when appropriate to change 13 to 1, 17 to 5, etc..
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
BlakeMcKennaAuthor Commented:
I actually want the military time format (even though I don't need the AM/PM)....
0
 
Scott PletcherSenior DBACommented:
If you just want military time, you can do:

CONVERT(varchar(8), datetime_column, 8) AS formatted_time,
0
 
BlakeMcKennaAuthor Commented:
It still didn't come out right. I'll just keep what I had.

Thanks!
0
 
BlakeMcKennaAuthor Commented:
This solution worked...but I also figured it out.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now