Solved

Extracting and Formatting a DateTime column in SQL Server 2008

Posted on 2015-01-14
7
134 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Merge Statement 3 40
Implementing SQL Server Data Files in Azure 1 36
Writing SQL Select Query result to a text file 12 32
email about the whoisactive result 7 30
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now