Help With Converting Numeric Minute Value To Time Value hh:mm 1

[wheels0323]

CASE WHEN i.TotalWeekHoursMinutes > MRW.MAXHOURSNUM THEN 'Worked more than ' + CAST(CONVERT(DECIMAL(5,2), MRW.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' total week hours' 
WHEN i.DayTotalMinutes > MRD.MAXHOURSNUM and i.applydate = i.schoolcalendardt THEN 'Worked more than '  + CAST(CONVERT(DECIMAL(5,2), MRD.MAXHOURSNUM / 60.00) AS VARCHAR(5))+ ' hours for one day'
WHEN i.StartDtmHours < MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt THEN 'Started before '  + CAST(CONVERT(DECIMAL(5,2), MRD.EARLIESTSTARTTM /60.00) as varchar(5))--+ ':00'
WHEN i.EndDtmHours > MRD.LATESTENDTM THEN 'Worked passed '  + CAST(MRD.LATESTENDTM / 60 as varchar(5))+ ':00 at night'
ELSE 'No Infraction'
END AS Infraction

In this code above Iam working on this line of code below.

WHEN i.StartDtmHours < MRD.EARLIESTSTARTTM and i.applydate = i.schoolcalendardt THEN 'Started before '  + CAST(MRD.EARLIESTSTARTTM /60 as varchar(5))+ ':00'

The MRD.EARLIESTSTARTTM field is a numeric value for a 24 hour clock. So, 1a.m. would be 60. 2a.m. would be 120. It's in minutes basically. When it hits Midnight, the value jumps back to zero.

My question is how to write this code differently so that it comes back in hh:mm format. The current way has no decimal. Yet I don't want decimal anyways.
I want it to come back in the 00:00 format. Military time is fine. Any suggestions? I was using some different type of code to do this, but the closest I got
still cut off the last zero of the time.

 

[wheels0323]

Convert(VarChar(2), MRD.EARLIESTSTARTTM  / 3600) + ':' + Convert(VarChar(2), MRD.EARLIESTSTARTTM  / 60 % 60)

This is what got me close. Yet, it brought back the right values. Just some values that were lets say 15:00 would come back as 15:0

 

[gmmastros]

Try this:

Convert(VarChar(5), DateAdd(Minute, @MRD.EARLIESTSTARTTM, 0), 108)

Basically, you are adding the number of minutes to Jan 1, 1900 at midnight. The convert (with style 108) removes the date portion and returns just the time. This also returns the seconds (which would be :00 in all cases). To remove the seconds, use varchar(5) so just the first 5 characters are returned (effectively truncating the seconds).

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

In the code I posted, I inadvertently left an @ symbol. This is a relic from my test code. Please remove.

Convert(VarChar(5), DateAdd(Minute, MRD.EARLIESTSTARTTM, 0), 108)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wheels0323]

You are so sexy when your right.
I love you.
Thank you