noon cutoff met or not met, having trouble calculating

[jdwalton]

I am developing a report that displays the number of records that met the noon cutoff and that did not meet the noon cutoff. I have started with a formula like this:
'if ({table.datefield} > '???'.....'

Everything I have tried where the '???' is does not work. I am hitting oracle 9.2 and the field is a date time field but all I need to calculate against is the time. I have tried:
"12:00:00"
Time(12,00,00)
TimeValue(12,00,00)
TimeValue(.5)

Obviously I am not getting the right syntax. I appreciate any feedback on this. Thanks in advance.

Jim

 

[midearth]

Try:

If time({table.datefield}) > time(12,00,00)

That should fix it.

 

[jdwalton]

Thanks midearth for the info. I have tried what you suggested and I get an error saying 'A date time is required here'. That's my frustration is that the obvious solutions are not working. Any other ideas? Thanks again for your help, I do appreciate it.

Jim

 

[midearth]

Where was the cursor placed within the formula when you got the error message?

 

[jdwalton]

Right before the word 'Time'

 

[dgillz]

This error is telling you that {table.Datefield} is not a date-time field. I am pretty sure it is simply a date field. If that is the case, and there is no time field in your database, what you are trying to do is impossible.

Look at the field through the data explorer, and make sure the top right hand icon is depressed. It will show you the data type and size of the object. This should confirm that this field is a date field, not a date-time field.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[midearth]

Thanks, dgillz. I was tied up on the phone and couldn't respond back. That was going to be my next suggestion.

 

[jdwalton]

I just wanted to followup on this post. I finally got my problem solved. The oracle date field is, in fact, a date-time field and in oracle, to return the date and time or the time only, you must define how you want it formatted, otherwise it only pulls back the date. That has nothing to do with my solution however. I found a function in Crystal called DateTimeToTime to see just the time in the date-time field. Then I do a calculation against that calculation to determine if the value is greater than noon or less than.

Thanks for all of your help, I do appreciate it.

Jim