COMMAND DATE RANGE - Yesterday 7AM to CurrentDate 7AM?

[LWTri]

Hello all.
In Crystal XI, I'm debugging my SQL COMMAND syntax:

This worked in Group Selection, but trying to use logic in a COMMAND instead:

{REG.START_DT_TM} >= datetime(currentdate - 1,time(7,0,0)) and {REG.START_DT_TM} <= datetime(currentdate,time(6,59,59))

Thanks for your help!

 

[Turkbear]

Hi,
Since Commands are processed by the database directly, the syntax is specific to that version--which database is being used?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[LWTri]

Hi Turkbear, forgot to include DB - Oracle.

 

[LWTri]

When simply searching for Oracle date formats, I couldn't find syntax for this purpose... but I finally searched for "concatenate date and time":

http://forums.oracle.com/forums/thread.jspa?messageID=4014345

I'm hoping something like this will work. I'll try it out. Please share if anyone has better solutions:

{REG.START_DT_TM} >= to_date(to_char(CURRENT_DATE - 1, 'MM-DD-YYYY') || '07:00:00', 'MM-DD-YYYY HH24:MI:SS') and
{REG.START_DT_TM} <= to_date(to_char(CURRENT_DATE, 'MM-DD-YYYY') || '06:59:59', 'MM-DD-YYYY HH24:MI:SS')

 

[lbass]

Try replacing current_date with: curdate or {fn curdate()}

-LB