BobRosas
asked on
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.
=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,
knightEknight
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.
ASKER
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.
ASKER
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(varch
substring((convert(varchar
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.
DATEDIFF(mi,(convert(varch
substring((convert(varchar
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thanks again!
ASKER
Thanks again! EE is Great!
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 :-) .
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 :-) .
ASKER
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!
You are truly a genius in my eyes. Thanks again!