Convert time from minutes since midnight 2

[cdiwindows]

I have a table that is replicated from a vendor. There is a field that is for the check open time and check close time. Both of these fields are based on the number of Minutes since midnight. I need to convert this to the standard hh:mm format and cannot find the SQL command to do this. I have tried : CheckOpen=cast(opentime/60 AS varchar)+ ':' +cast(opentime % 60 AS varchar)

But the minute do not display correctly and have it in the datatype datetime.
For example open time = 963 displays as 16:3

Any help would be appreciated.

 

[gmmastros]

Try this:

CheckOpen = Convert(VarChar(5), DateAdd(Minute, OpenTime, 0), 108)

First, notice this part:

[tt][blue]DateAdd(Minute, OpenTime, 0)[/blue][/tt]

This will add the value in OpenTime to 0. The 0 actually represents a DateTime value. In SQL Server, this is Jan 1, 1900. Then we convert this to a varchar(5) using style 108. Style 108 is hh:mi:ss. By converting this to varchar length 5, we will get the first 5 characters of the conversion which happens to be hours and minutes.

Make sense?

-George

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

 

[cdiwindows]

Thank you George! That is exactly what I was looking to do.

 

[PWise]

Thank you George

Have A Pink one

How Can I do this for more the 24 hours

 

[gmmastros]

pwise,

Can you show your inputs and expected output? When the time exceeds a day, what would you like the output to look like?


-George

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

 

[PWise]

my inputs are minutes

Select empid,Sum(datediff(n,StartTime,endtime))
From EmpClocking
Group By Empid ,datepart(ww,StartTime)

Results
1 1439-->>23:59
2 1450-->>24:10
3 2880-->>48:00

 

[PWise]

one more

4 1445-->>24:05

 

[gmmastros]

The advice I gave earlier will perform better that what I am about to suggest.

For this, I would suggest a scalar function. Since this doesn't use data from a table, it should perform pretty well.

The function...

Create Function dbo.FormatTime
	(@Minutes Int)
Returns VarChar(20)
As
Begin
	Return Convert(VarChar(10), @Minutes / 60) + ':' + Right('0' + Convert(VarChar(2), @Minutes % 60),2)
End

Then, to use it...

Select empid,[!]dbo.FormatTime([/!]Sum(datediff(n,StartTime,endtime))[!]) As Duration[/!]
From EmpClocking
Group By Empid ,datepart(ww,StartTime)

-George

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

 

[PWise]

Thanks George:

but I was looking for something without so many converts and concatanations functions
something like your line for <24:00

Well you have my star alredey