Time Selection

[socalvelo]

CR XI
Oracle DB

Problem: I have a field that is calculated in the db to create what is called a split dateTime. This is calculated from two other fields StartDateTime and EndDateTime. The ranges between these fields can last several days. When I try to use the split date time (in certain date ranges), I receive a Database Connection error that indicates "hour must be between 0 and 23." I think that one of these calculations in the database resulted in a midnight time of either 00:00:00 or 24:00:00 in one or more records.

I am having trouble writing a formula for the selection editor to not use any "time" not in the 1 to 23 hour range. I realize I can create my own split time formulas from the Start and Ending Date/Time field which I had done in the past before the DBA created the splitTime field.

The Selection Formula I am experimenting with: (timevalue({Split.dateTime})) in timevalue(00,00,01) to timevalue(23,59,59)

This doesn't seem to solve the problem. Any thoughts?

 

[beacon5]

Since the error message said the "hour must be between 0 and 23," have you tried using a timevalue range of (00,00,00) to (23,00,00) instead of the (00,00,01) to (23,59,59) timevalue you listed at the end of your post? It seems like, if you were using (23,59,59), which is basically 24, that you've exceeded the 0 to 23 limit the error message described since it's a zero-based range and not a one-based range.

If (00,00,00) to (23,00,00) doesn't work, you might try (00,00,01) to (22,59,59).

 

[socalvelo]

Beacon5 - thanks for the response. I still receive the error when attempting both of your suggestions. I also threw in a selection formula to exclude null fields.

 

[beacon5]

If you browse the data in the {Split.datetime} field, what's an example of how the data is stored? Does it list time values separated by a comma or a semicolon?

Also, I know that the field is working with date/times, but is that the data type for the field? Or is it some other data type, like string, etc.?

 

[lbass]

When I try to use the split date time (in certain date ranges), I receive a Database Connection error that indicates "hour must be between 0 and 23."

How exactly are you using the splitdatetime field?

You should not have to correct for a field.

-LB

 

[socalvelo]

The formatting is Date/Time. When browsing the field, the date and time are separated by a space. Time is separated by semicolons.

 

[socalvelo]

I just narrowed this down to a data input error that allows a time of 24:00 to be used during data entry in the StartTime Field. I only came across this because of the date range I was attempting to use. Thanks for the assistance, it helped me narrow down the problem. I created a selection formula that extracted the use of 24:00 in the StartTime field and it solved the problem.