Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

wheels0323

Programmer
Oct 15, 2008
17
US
Code:
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.

Code:
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.
 
Code:
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
 
Try this:

Code:
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
 
In the code I posted, I inadvertently left an @ symbol. This is a relic from my test code. Please remove.

Code:
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
 
You are so sexy when your right.
I love you.
Thank you
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top