NEXT_DAY in Crystal Reports

Question

Is there anything like the Oracle "NEXT_DAY" function available in the syntax that Crystal Reports uses?

I'm trying to write a formula to output the following Monday @ 9:00am if the datetime tested falls between Friday @ 9:00pm and Monday @ 9:00am.

So far I have

IF DAYOFWEEK ({DATETIMEFROMMYDB}) IN [7,1]
    OR (DAYOFWEEK({DATETIMEFROMMYDB}) = 6 AND TIME({DATETIMEFROMMYDB}) in time(21,00,00) to time(23,59,59))
    OR (DAYOFWEEK({DATETIMEFROMMYDB}) = 2 AND TIME({DATETIMEFROMMYDB}) in time(00,00,00) to time(08,59,59))
THEN ...

I know I can write seperate IF statements to do a different amount of DateAdd for each of Fri, Sat, Sun, Mon, but if I can keep it concise by lumping all of these into one I would much prefer it. I'm already going to be adding additional rules for if the datetime falls outside of business hours on the other weekdays so I want to do as much as possible to prevent this from becoming a very overgrown and ugly formula.

Answer 1

Since there is no CR equivalent that I know of, you can just cheat and borrow the NEXT_DAY() function from the Oracle database. You can do this by creating a SQL Expression and then entering something like:

-- SQL Expression {%NextDay}
(SELECT NEXT_DAY("MYTABLE"."MYDATETIME", 'MONDAY')
 FROM DUAL)

then you could either use that directly in your formula:

IF DAYOFWEEK ({MYTABLE.MYDATETIME}) IN [7,1]
    OR (DAYOFWEEK({MYTABLE.MYDATETIME}) = 6 AND TIME({MYTABLE.MYDATETIME}) in time(21,00,00) to time(23,59,59))
    OR (DAYOFWEEK({MYTABLE.MYDATETIME}) = 2 AND TIME({MYTABLE.MYDATETIME) in time(00,00,00) to time(08,59,59))
THEN DateTime(date({%NextDay}),time(09,00,00))

Or, the even better way would be to just stuff ALL of the logic into the SQL Expression and do away with the formula altogether.