Convert int to military time

How do I convert and int to military time?

example:

Int
7      = 0700
19    = 1900
23    = 2300
MikeM670Asked:
Who is Participating?
 
Vitor MontalvãoMSSQL 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' 

Open in new window


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

(1 row(s) affected)
0
 
Vitor MontalvãoMSSQL 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

Open in new window

0
 
Lee SavidgeCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MikeM670Author 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
 
Pawan KumarDatabase ExpertCommented:
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
 
MikeM670Author 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

Open in new window

0
 
MikeM670Author Commented:
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
 
MikeM670Author 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.

All Courses

From novice to tech pro — start learning today.