We help IT Professionals succeed at work.

DateDiff issue on a generated field

I have the attached code as part of my query and it gives me the Hours and Minutes an employee was on break if they went over 15 minutes.  This part works.  Now I need to add another field on the report that just shows the amount they are over.  For example the HrMin field may show 0:18 for HrMin (or how long they were on break) and now I want a column to show how much of that was over the allowed amount (15 minutes).  I added a column with the following expression (instead of adding it to the query) but get an error...
   =IIf( Fields!Type.Value = "Break", DateDiff("nn", Fields!HrMin.Value, 15), 0)
Is it a format issue?  Please show me how to fix this.
convert(varchar,datediff(hh,0,(dateadd(ss,sum(ts_seconds) ,0))))+ 
	     substring((convert(varchar(8),(dateadd(ss,sum(ts_seconds) ,0)),114)),3,3) as HrsMin,

Open in new window

Comment
Watch Question

Not sure if this is the entire problem, but your datediff needs to use "mi" instead of "nn", and if that is SQL then don't surround it with quotes.

Author

Commented:
Thank you for your quick response.  My query is sql but I entered the code as an expression in a report services field.  The code does complile as is but I think it's a format issue is why I get an error.  I tried changing to mi without quotes and it wouldn't even compile.

Author

Commented:
Instead of trying to do the code in report services I am trying it in my SQL code.  So I used your code suggestion and added this code to my query...

DATEDIFF(mi,(convert(varchar,datediff(hh,0,(dateadd(ss,sum(ts_seconds) ,0))))+
           substring((convert(varchar(8),(dateadd(ss,sum(ts_seconds) ,0)),114)),3,3)),convert(varchar(8),15,114)) as TimeOver

Basically I took the previous line of code that created my HrsMin field (see initial post) and tried to create a date diff.
It compiles but I get the following error when I try to run it...

Msg 241, Level 16, State 1, Procedure spBreakTEMP, Line 11
Conversion failed when converting date and/or time from character string.

I can't get my format problem figured out.  I would really appreciate some help.
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Please try adding this to your SQL SELECT:

convert(varchar(5), dateadd(minute, -15, dateadd(second, sum(ts_seconds), 0)), 8) AS [HH:MM_Over]

Then naturally use that as the source field for another report column.

Author

Commented:
Wow!  Wonderful!  Thank you!  You simplified it so much and it still works.  I've increased points in hopes you will help me if I need to post a related question.  Now that I've got breaks working I need to figure out 30 and 60 minute lunches as well.
Thanks again!

Author

Commented:
Thanks again!  EE is Great!
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
You're welcome!

Btw, as you probably noticed, you can simplify the original code too:


convert(varchar(5), dateadd(second, sum(ts_seconds), 0), 8) as HrsMin,


Yeah, that's a bit easier to read :-) .

Author

Commented:
That is Awesome!  Thank you!  No I did not notice I could simplify the original code as well.  I will be changing that also.  I admit I struggle to understand a lot of the code I've gotten help with.  If it works I leave it and hope one day it will all be more clear. : )
You are truly a genius in my eyes.  Thanks again!