0

I was using Hour(Fields!tempo.Value) for get the hour from a field (tempo). The problem came when I got for first time "24:00" and the report shows the '#error'.

The description in 'Hour' function said:

Returns an Integer value from 0 through 23 representing the hour of the day

So, I changed the expression to

=IIf(Fields!tempo.Value = "24:00", 0, Hour(Fields!tempo.Value))

but yet I get the #error. Then I tried:

=IIf(Fields!tempo.Value = "24:00", 0, 1)

and correctly I get "0" for 24:00 values and "1" for the rest.

  1. I don't understand why Hour() won't work under IIf().
  2. Maybe exist some better workaround for this case and I don't know it.

Thanks in advances

EDIT:

The idea is get all events which started between 2 hours every day (for filter events from the morning, afternoon and night):

=IIf(Hour(Fields!tempo.Value) >= 22 Or Hour(Fields!tempo.Value) <= 6, SHOWTHIS, Nothing)
HEDMON
  • 727
  • 10
  • 24
  • Is the "24:00" coming from the database and what data type is that field? And if the data is the result of a stored proc, you should convert it in t-sql prior to the report. – SS_DBA Oct 20 '16 at 12:38
  • the field is varchar and it isn't from a stored procedure. – HEDMON Oct 20 '16 at 12:44
  • Embedded query in SSRS? can you show it? – SS_DBA Oct 20 '16 at 12:50
  • is 24:00 a time of day? It is often considered to be incorrect, and said that it should be written as 00:00. – Cato Oct 20 '16 at 12:59
  • @WEI_DBA, I'm not sure if I can share the query, but I edited the question with the final expression I'm using and I think is more clear. If yet do you have some question let me know. Thanks very much for your time. – HEDMON Oct 20 '16 at 13:27
  • @AndrewDeighton, the form in the app where people enter the time don't have this kind of validation. Somebody write "00:00", others "24:00". – HEDMON Oct 20 '16 at 13:29
  • @HEDMON - I think I know what's wrong, IIF always evaluates both the arguments you pass to it - Therfore you can't use a condition to prevent a runtime error - does IF work the same way there? – Cato Oct 20 '16 at 13:39
  • Hour(IIf(Fields!tempo.Value = "24:00", "00:00", Fields!tempo.Value)) this might avoid HOUR ever acting on '24:00' - it's a problem with IIF, it always evaluates both sides, regardless of the true/false condition, then discards the false one. – Cato Oct 20 '16 at 13:42
  • @AndrewDeighton you are right, and thanks to your comment I understand now the solution from alejandro – HEDMON Oct 20 '16 at 14:07

1 Answers1

1

if you don't need to perform operations with the hour you can work with strings to get the hour, also if your dataset returns a string this is the simplest solution (IMO).

Try using this expression:

=IIF(LEFT(Fields!tempo.Value,2)="24","0",REPLACE(LEFT(Fields!tempo.Value,2),":",""))

If you still want to get the hour using HOUR function you have to validate twice your field, in the IIF and inside the HOUR function:

=IIF(Fields!tempo.Value="24:00",
0,
HOUR(IIF(Fields!tempo.Value="24:00","0:00",Fields!tempo.Value))
)

Let me know if this helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • Gracias! I will accept the answer @Andrew because solve directly the problem, but I appreciate your time and even if was a littler more longer, it's also a solution. Thank you very much. You have my UV ;) – HEDMON Oct 20 '16 at 13:59
  • @HEDMON, The answer (actually a comment) posted by Andrew 15 minutes ago is the same answer I posted 40 ~ minutes ago. Good luck. – alejandro zuleta Oct 20 '16 at 14:00
  • Oh! Sorry, I was looking just the first version of your answer, but you are right. Accepted. – HEDMON Oct 20 '16 at 14:04
  • not exactly the same as what I posted - it has a section that is similar - but I'm glad it all helps! – Cato Oct 20 '16 at 14:56