Solved

SQL get military time from string time - SQL Server 2005

Posted on 2011-03-23
8
643 Views
Last Modified: 2012-06-22
Hello experts,

I have a time field that I need to convert into military 4-digit 4-char varchar.  I currently have:

table: appointments
time:
03:20PM
03:30PM
03:40PM
03:50PM
08:00AM
08:10AM
08:20AM
08:30AM

and I would like to land on:

table: appointments
time:
1320
1330
1340
1350
0:00
0810
0820
0830

Thoughts?

Thanks!
0
Comment
Question by:robthomas09
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
What is the data type of time column?
0
 

Author Comment

by:robthomas09
Comment Utility
it is varchar(255) - thanks!
0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
Comment Utility
If the data type is varchar,try this.
select replace(left(convert(varchar,CONVERT(datetime,[time]),108),5),':','') as TimePart from appointments

Open in new window

If the data type is datetime, try this.
select replace(left(convert(varchar,[time],108),5),':','') as TimePart from appointments

Open in new window

0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 150 total points
Comment Utility
Try this:
select REPLACE(CONVERT(char(5), CONVERT(datetime, appt_time), 8), ':', '')
from appointments;

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Sorry, Sharath.  I started typing my post before I saw yours.
0
 
LVL 4

Assisted Solution

by:MaximumIQ
MaximumIQ earned 100 total points
Comment Utility
If you're trying to do this directly in SQL I wouldn't know. But you can always use a VB Script or a simple VB solution to pull the fields, convert them and overwrite the table. The actual conversion in VB would look something like this.  

Dim strTime As String
Dim intSeperator As Integer

For Each strTime In TimeList
    intSeperator = InStr(Item, ":")
    
    Select Case UCase(Right(strTime, 2))
        Case Is = "AM"
            strNewTime = Replace(Replace(strTime, ":", ""), "AM", "")
        Case Is = "PM"
            strNewTime = Int(Left(strTime, intsperator - 1)) + 12 & Mid(strTime, intSeperator, 2)
        Case Else
            strNewTime = strTime
    End Select
Next Item

Open in new window

0
 
LVL 4

Expert Comment

by:MaximumIQ
Comment Utility
^ Late as always
0
 

Author Closing Comment

by:robthomas09
Comment Utility
Thanks!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

728 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

9 Experts available now in Live!

Get 1:1 Help Now