CODEWITHSUNDEEP
sqltimesubtraction
lugeno
lugeno

Reputation: 1065

SQL Server 2008 - Difference between time(0)

I've a table with working_hours time(0), lunch_hours time(0) What I have to do is the following:

If lunch_hours is greater that one hour, I have to calculate the offset

Example:

lounch_hour = 01:30:00 => offset = 00:30:00

Once done I've to subtract the offset from the working_hours value

Example:

offset = 00:30:00, working_hours = 07:30:00 => working_hours = 07:00:00

The result must be in time(0) format (hh:mm:ss) I've tried several solutions but still not working. Used DATEDIFF probably didn't used in correct way.

Thanks for any help

Upvotes: 0

Views: 5670

Answers (1)

GarethD
GarethD

Reputation: 69789

You are using TIME to store duration, they are not really comparable, and there may be situations where you come unstuck, the main example of this is that when you have a duration over 24 hours using a time format falls down, it would be much better to store duration in decimal seconds (or minutes or hours), then you can use various methods either in sql, or perferably in your application layer to convert this to a HH:mm:ss format.

However, for your exact scenario you could use:

DECLARE @WorkingHours TIME(0) = '07:00',
        @LunchHours TIME(0) = '01:30';

SELECT  Total = DATEADD(MINUTE, DATEDIFF(MINUTE, @LunchHours, '01:00'), @WorkingHours);

Basically calculate the difference between the allowed lunch hour and the actual lunch hour in minutes, then add these minutes on to the working hours.

Upvotes: 1

Related Questions