Solved

Convert int to military time

Posted on 2016-11-25
8
35 Views
Last Modified: 2016-11-25
How do I convert and int to military time?

example:

Int
7      = 0700
19    = 1900
23    = 2300
0
Comment
Question by:MikeM670
8 Comments
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41901695
It's only for the hours? No minutes?
If so you can try this:
SELECT REPLICATE('0', 2-LEN(CAST(ColumName AS VARCHAR(4))))+CAST(ColumName AS VARCHAR(4)) + '00'
FROM TableName

Open in new window

0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 41901706
If it is just the hours then:

select right('0' + cast((colname * 100) as nvarchar(2)), 4)

Open in new window


Basically multiply by 100 and prefix with a 0 and return the right 4 chars
0
 

Author Comment

by:MikeM670
ID: 41901716
Victor,

Yes it's only for the hours.  I'm using a stored procedure that uses:
SET @8hrfirstshiftstart = 7
SET @8hrfirstshiftend = 15
ect....

to find the start and end of shifts.   But I want to return the  7 or other values from the stored procedure to use as labels in crystal reports.

Like this...

0700 - 1500
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

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41901721
Try.. Easiest One :)

DECLARE @T AS INT = '7'
SELECT LEFT(@t * 1000,4)

Open in new window


trial


DECLARE @T AS INT = '7'
SELECT LEFT(@t * 1000,4)

GO

DECLARE @T AS INT = '19'
SELECT LEFT(@t * 1000,4)

GO

DECLARE @T AS INT = '23'
SELECT LEFT(@t * 1000,4)

GO

DECLARE @T AS INT = '1'
SELECT LEFT(@t * 1000,4)

Open in new window


Output

----
7000

(1 row(s) affected)


----
1900

(1 row(s) affected)


----
2300

(1 row(s) affected)


----
1000

(1 row(s) affected)

Open in new window

0
 

Author Comment

by:MikeM670
ID: 41901726
Pawan,

Unfortunately that won't work for time values like

7    =    0700
1    =    0100

----
7000

(1 row(s) affected)


----
1900

(1 row(s) affected)


----
2300

(1 row(s) affected)


----
1000

Open in new window

0
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41901730
So use this in your SP:
DECLARE @8hrfirstshiftstart INT = 7
DECLARE @8hrfirstshiftend INT = 15

SELECT REPLICATE('0', 2-LEN(CAST(@8hrfirstshiftstart AS VARCHAR(4))))+CAST(@8hrfirstshiftstart AS VARCHAR(4)) + '00 - '
     + REPLICATE('0', 2-LEN(CAST(@8hrfirstshiftend AS VARCHAR(4))))+CAST(@8hrfirstshiftend AS VARCHAR(4)) + '00' 

Open in new window


Result:
--------------------------------
0700 - 1500

(1 row(s) affected)
0
 

Author Comment

by:MikeM670
ID: 41901731
Lee,

Arithmetic overflow error converting expression to data type nvarchar

Open in new window



Returns a error:
Arithmetic overflow error converting expression to data type nvarchar
0
 

Author Closing Comment

by:MikeM670
ID: 41901732
That is exactly what I needed.  Thanks!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

806 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