Solved

Military time as regular time

Posted on 2009-05-18
3
522 Views
Last Modified: 2012-05-07
I have a table I'm able to select on only
It has 2 columns and an id
the columns are in military time...but are varchar(200)

I need to do a select and output as regular time with a space and AM or PM

Example

ckin
1600

needs to be output at 4:00 PM
0
Comment
Question by:lrbrister
[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
  • 3
3 Comments
 
LVL 15

Expert Comment

by:MNelson831
ID: 24416536
Declare @Datafield as varchar(50)

Set @DataField = '1600'

select
      Case
            when left(@DataField,2) - 12 < 0 then Left(@DataField,2)
            Else Left(@DataField,2)
      End + ':' + right(@DataField,2)
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 24416546
OOps

Try this instead

Declare @Datafield as varchar(50)

Set @DataField = '1600'

select
      Case
            when left(@DataField,2) - 12 < 0 then Left(@DataField,2)
            Else Left(@DataField,2) -12
      End + ':' + right(@DataField,2)
0
 
LVL 15

Accepted Solution

by:
MNelson831 earned 500 total points
ID: 24416571
Guess I should read slower... missed the AM/PM part


Declare @Datafield as varchar(50)

Set @DataField = '1600'

select
      Case
            when left(@DataField,2) - 12 < 0 then Left(@DataField,2)  + ':' + right(@DataField,2) + ' PM'
            Else Left(@DataField,2) -12 + ':' + right(@DataField,2) + ' AM'
      End as NewTimeValue
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

630 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