Convert int to military time

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

Select allOpen in new window

If it is just the hours then:

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

Select allOpen in new window

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

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

Try.. Easiest One :)

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

Select allOpen 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)

Select allOpen in new window

Output

----
7000

(1 row(s) affected)


----
1900

(1 row(s) affected)


----
2300

(1 row(s) affected)


----
1000

(1 row(s) affected)

Select allOpen in new window

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

Select allOpen in new window

Lee,

Arithmetic overflow error converting expression to data type nvarchar

Select allOpen in new window

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

That is exactly what I needed.  Thanks!