Solved

SQL get military time from string time - SQL Server 2005

Posted on 2011-03-23
8
668 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
[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
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35202066
What is the data type of time column?
0
 

Author Comment

by:robthomas09
ID: 35202091
it is varchar(255) - thanks!
0
 
LVL 41

Accepted Solution

by:
Sharath earned 250 total points
ID: 35202104
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 60

Assisted Solution

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

Open in new window

0
 
LVL 60

Expert Comment

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

Assisted Solution

by:MaximumIQ
MaximumIQ earned 100 total points
ID: 35202203
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
ID: 35202213
^ Late as always
0
 

Author Closing Comment

by:robthomas09
ID: 35202282
Thanks!
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
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…

624 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