?
Solved

Extracting and Formatting a DateTime column in SQL Server 2008

Posted on 2015-01-14
7
Medium Priority
?
147 Views
Last Modified: 2015-01-14
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
Comment
Question by:BlakeMcKenna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40550088
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
 

Author Comment

by:BlakeMcKenna
ID: 40550123
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40550160
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:BlakeMcKenna
ID: 40550181
I actually want the military time format (even though I don't need the AM/PM)....
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40550192
If you just want military time, you can do:

CONVERT(varchar(8), datetime_column, 8) AS formatted_time,
0
 

Author Comment

by:BlakeMcKenna
ID: 40550213
It still didn't come out right. I'll just keep what I had.

Thanks!
0
 

Author Closing Comment

by:BlakeMcKenna
ID: 40550215
This solution worked...but I also figured it out.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question