Date Range Select Problem

[mipsfuct]

Version: Crystal 10
Database: SQL

I currently have a report that uses:

{Deal_Jacket.title_comp_pend} in cdatetime(year(currentdatetime),month(currentdatetime),day(currentdatetime-1),7,0,0) to cdatetime(year(currentdatetime),month(currentdatetime),day(currentdatetime),7,0,0)

- In order to select between 7:00AM yesterday and 7:00AM today.

The problem is that this formula will not work on the First of the Month. For example, today (March 1st) it select a date range of (3/28/2005 to 3/1/2005) due to the simplistic subtraction of the day-1.

I think this is beyond my current skills to get this done. Any help greatly appreciated!!

Dave

 

[kskid]

I use the following in my record selection formula

{TABLE.DATETIME} in datetimevalue(currentdate - 1,timevalue(07,00,00)) to_ datetimevalue(currentdate,timevalue(07,00,00))

Cheers,
-LW

 

[kskid]

Forgot to mention. Please note that I have to_, which means to exclude the ending value but will be selected in tomorrow's report.

Cheers,
-LW

 

[synapsevampire]

Try:

{Deal_Jacket.title_comp_pend} in dateserial(year(currentdatetime),month(currentdatetime),day(currentdatetime)-1,7,0,0) to cdatetime(year(currentdatetime),month(currentdatetime),day(currentdatetime),7,0,0)

Dateserial handles it nicely.

-k

 

[mipsfuct]

Thanks KSKID, that seems to work nicely, you saved me another nights reading!

Synapse - I had a problem with that one, (too many arguments)...seems to be the "-1".
Maybe I copied it wrong? Many Thanks.

 

[synapsevampire]

Sorry, I forgot that dateserial doesn't handle the time portion:

{Deal_Jacket.title_comp_pend} in dateserial(year(currentdatetime),month(currentdatetime),day(currentdatetime)-1),timevalue(7,0,0) to cdatetime(year(currentdatetime),month(currentdatetime),day(currentdatetime),7,0,0)

Since you're just going back a day, I'd go with kskid's solution, but keep in mind the dateserial function as it helps immensely with months/years.

-k

 

[mipsfuct]

Cool, thanks a lot...

These are my first reports; you gave me a few things to ponder reagrding the months/years, I will play around with this tonight.

Thanks again!