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
Solved

Extracting and Formatting a DateTime column in SQL Server 2008

Posted on 2015-01-14
7
140 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
  • 4
  • 3
7 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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