SQLTeam.com | Weblogs | Forums

Comparing two sets of times within 24 hours


#1

Hi Everyone,

I made an earlier post regarding this issue and it seemed to work but I got this bug

=IIF(DateDiff("h", ReportItems!Textbox1.Value,ReportItems!Textbox2.Value) <= 24, "WITHIN 24",

IIF(
DateDiff("h", ReportItems!Textbox1.Value,ReportItems!Textbox2.Value) > 24
AND
DateDiff("h", ReportItems!Textbox1.Value,ReportItems!Textbox2.Value) <= 48, "24 TO 48", "OVER 48"))

these two dates should be "24 TO 28 HOURS" but instead its giving me "WITHIN 24"

I am not sure why it would give me this answer
Any help would be extremely helpful

Thanks,
M


#2

It works fine in SQL
DECLARE @Date1 datetime = '2016-11-13 12:30'
DECLARE @Date2 datetime = '2016-11-14 13:00'

SELECT DATEDIFF(Hour,@Date1,@Date2)

result is : 25
but the environment you use i not works fine

try to cast the value of TextBox1 and TextBox2 to DateTime at first
or use DateTime control to easy way

so
use this
(ReportItems!Textbox2.Value - ReportItems!Textbox1.Value).Hours

you must found Hours method to get the different between to dates by hours

Please let me know what the type of your code ? this is not sql query .


#3

Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox3.Paragraphs[0].TextRuns[0]’ contains an error: Argument 'Date1' cannot be converted to type 'Date'.

So I would be the convert to Datetime somehow

Found a solution

=IIF(DateDiff("h", ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!Textbox11.Value)& ":" & DateDiff("n", ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!Textbox11.Value) mod 60 <= 24 &":"& 00, "WITHIN 24", IIF( DateDiff("h", ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!Textbox11.Value)& ":" & DateDiff("n", ReportItems!PROSPECT_ADMIT_DATE.Value,ReportItems!Textbox11.Value) mod 60 > 48 &":"& 00, "OVER 48", "24 TO 48"))