How to Take Part of a String using One of the T-SQL Text Functions

Question

I have the following snippet:

 CONVERT(varchar, FLOOR(ROUND((DATEDIFF(minute, shiftStart.timeEntered, shiftEnd.timeEntered) - DATEDIFF(minute, 
                      lunchStart.timeEntered, lunchEnd.timeEntered)) * 1.0 / 60, 2, 1))) + ':' + CONVERT(varchar, ROUND(ROUND(DATEDIFF(minute, shiftStart.timeEntered, 
                      shiftEnd.timeEntered) * 1.0 / 60, 2, 1) % 1 * 60, 0)) AS WorkingHours

The above code prints hour and minute in the following format:

4:30.000000
7:40.000000
8:1.000000

I need to rid of the . and next 0's so i get the following format:

4:30 (4 hours and 30 mins)
7:40 (7 hours and 40 minutes)
8:1 (means 8 hours and 1 minute)

.0000 Doesn't harm but it gives bad look

OMG Ponies · Accepted Answer · 2010-10-13 23:36:59Z

1

Convert the minutes portion to INTeger before converting to VARCHAR to drop the decimal values:

CONVERT(VARCHAR, 
        CONVERT(INT, ROUND(ROUND(DATEDIFF(minute, shiftStart.timeEntered, shiftEnd.timeEntered) * 1.0 / 60, 2, 1) % 1 * 60, 0)))

Full example:

CONVERT(varchar, FLOOR(ROUND((DATEDIFF(minute, shiftStart.timeEntered, shiftEnd.timeEntered) - DATEDIFF(minute, lunchStart.timeEntered, lunchEnd.timeEntered)) * 1.0 / 60, 2, 1))) + ':' + 
CONVERT(VARCHAR, CONVERT(INT, ROUND(ROUND(DATEDIFF(minute, shiftStart.timeEntered, shiftEnd.timeEntered) * 1.0 / 60, 2, 1) % 1 * 60, 0))) AS WorkingHours

answered Oct 13, 2010 at 23:36

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to Take Part of a String using One of the T-SQL Text Functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related