Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Extracting and Formatting a DateTime column in SQL Server 2008

Posted on 2015-01-14
7
Medium Priority
?
148 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 70

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 70

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

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

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

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.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

618 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