# Convert int to military time

How do I convert and int to military time?

example:

Int
7      = 0700
19    = 1900
23    = 2300
###### Who is Participating?

MSSQL Senior EngineerCommented:
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

MSSQL Senior EngineerCommented:
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

Commented:
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 Commented:
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

Database ExpertCommented:
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 Commented:
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

Author Commented:
Lee,

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

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

Author Commented:
That is exactly what I needed.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.