Hour must be between 0 and 23

[gbankos]

I am trying to write a report that does a datediff or dateadd, such as:
Dateadd("d", 1, {@DateTimeTaken}) < CurrentDateTime.

The error message I get is 'Hour must be between 0 and 23'.

Now, the formula field I created that is causing the problem is:
@datetimetaken
datetime(CDate({@DateTaken(requested)}),{@timetakendisplay})

@timetakendisplay is:
Time(ToNumber(Left({@TimeTaken},2)),ToNumber(Mid({@TimeTaken},3,2)),ToNumber(Right({@TimeTaken},2)))

@timetaken is:
if {WO_HEADER.Estimated_Complete_Time}="" then "000001" else {WO_HEADER.Estimated_Complete_Time}
WO_HEADER.estimated_complete_time is a varchar(8) field

If I comment out my formula so I can get it to run, my values for @timetaken are:
081500
092000
105000
110600
111600
111800
080000
090000
091500
111900

When I put @timetakendisplay on the report, I do not get errors and the time is displaying properly.

So I am stumped as to why my formula is giving me this error. Everything looks legit. Any help would be appreciated and I thank you in advance.

 

[lbass]

Two questions. You say it is a varchar(8), and yet it appears that the field only has 6 characters. You might want to test the length of the field.

Also, can estimated complete time be null? Is so, you'd have to account for that.

-LB

 

[gbankos]

Thanks for responding. Length command brings back values of 6. There are no nulls in my data.

 

[lbass]

Can there be spaces? Maybe try:

if trim({WO_HEADER.Estimated_Complete_Time}) = "" then
"000001" else
trim({WO_HEADER.Estimated_Complete_Time})

-LB

 

[gbankos]

I added that, but still get the error. I do not know if there can be spaces - this is day 3 on the job for me and I'm not sure how to get to this data through the app they use.

However, the data that comes back does not show any record with "000001". It is all good-looking data. What stumps me is that the @timetakendisplay formula is showing a time on the report, so the data seems to be good.

 

[lbass]

What is the formula for {@DateTaken(Requested)}? How does it display if placed alone on the report? Please show some samples.

-LB

 

[gbankos]

{@DateTaken(Requested)} is
totext(cdate({WO_HEADER.WO_Date_List2}))

WO_HEADER.WO_Date_list2 is a datetime. The problem is the application does not let them specify the time part of the datetime, so all the times are 12:00:00 AM. They have this separate varchar field where they can put in the time. Crazy, but I didn't write this app.

There is a parameter/record selection criteria to run for a starting/ending date range. I am running it for today, so all the dates are showing as 6/19/2008.

So basically my @datetaken(requested) field is showing as 6/19/2008. My times are showing as I listed above. When I put these 2 formulas together to in another datetime formula, they look fine. However, when I try to do a datediff or dateadd command - error...

 

[lbass]

You are taking a date, making it a text field, then a date again. Instead, just try this:

//@datetimetaken:
datetime(date({WO_HEADER.WO_Date_List2}),{@timetakendisplay})

Not sure it will help, but let's see.

-LB

 

[gbankos]

I want to go on record as saying I didn't write this report, I was asked by the author to try to figure out why this report errors. I'll have to ask the author why she's taking that field from a date to text.

Anyway,
I made that change and it still errored. I also tried putting in a formula if the hour value is < 0 or > 24, make it 1 and then I got the same type of error on the minute value.

 

[lbass]

Please test for nulls on both date and time fields and then also show a sample of the results of each original database field (before any formulas are applied). Try sorting by each field, so you can see the extremes of each field. I also noticed that the sample you did show for times only showed times <= 12, as if it were not written in military time. Do you think the field includes an AM/PM that isn't getting picked up somehow?

-LB

 

[gbankos]

I've tested for nulls. There are none. Here's the original database fields. Note that the wo_date_list2 field is a datetime field and all times are 12:00:00 am. I also, I ran this report for just 6/20, so all wo_date_list2 values are 6/20/08:

WO_date_list2 estimated_complete_time my formula
6/20/2008 6/20/08 12:01am
094000 6/20/08 9:40:00am
080000 6/20/08 8:00:00am
095900 6/20/08 9:59:00am
101600 6/20/08 10:16:00am
101900 6/20/08 10:19:00am
102200 6/20/08 10:22:00am
095900 6/20/08 9:59:00am
080800 6/20/08 8:08:00am
081100 6/20/08 08:11:00am
080600 6/20/08 08:06:00am

You asked about am/pm. I ran the report for a larger date range and I do have times > 12 that show as PM on my report. I don't think it is an am/pm thing.

 

[lbass]

Are you using CR XI? If so, when the error occurs, what value for month do you see in the workshop tree that shows the error?

-LB

 

[gbankos]

I am using CR XI, yes. However, I can't run the report within CR XI because the application I'm integrating this with creates the data and stores it into a temp table I run off of.

 

[lbass]

What formula did you use to check for nulls? I think it has to be that.

-LB

 

[gbankos]

I did an IsNull. I also check the properties of the database and the column is not nullable. There is a default constraint on the column set to ' '.

I thank you for your time. I worked around it by converting the value to minutes and subracting them like this:
ToNumber((((Hour({@timearriveddisplay}) * 3600) + (Minute({@timearriveddisplay}) * 60) + Second({@timearriveddisplay}))
- ToNumber(((Hour({@timetakendisplay}) * 3600) + (Minute({@timetakendisplay}) * 60) + Second({@timetakendisplay}))))
/ 60)
This worked.