Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Convert int to military time

Posted on 2016-11-25
Medium Priority
65 Views
How do I convert and int to military time?

example:

Int
7      = 0700
19    = 1900
23    = 2300
0
Question by:MikeM670
[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

LVL 52

Expert Comment

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
``````
0

LVL 25

Expert Comment

ID: 41901706
If it is just the hours then:

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

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

Author Comment

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

LVL 30

Expert Comment

ID: 41901721
Try.. Easiest One :)

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

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)
``````

Output

``````----
7000

(1 row(s) affected)

----
1900

(1 row(s) affected)

----
2300

(1 row(s) affected)

----
1000

(1 row(s) affected)
``````
0

Author Comment

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
``````
0

LVL 52

Accepted Solution

Vitor Montalvão earned 2000 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'
``````

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

(1 row(s) affected)
0

Author Comment

ID: 41901731
Lee,

``````Arithmetic overflow error converting expression to data type nvarchar
``````

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

Author Closing Comment

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

## Featured Post

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
###### Suggested Courses
Course of the Month6 days, 7 hours left to enroll