cannot change End date parameter in record selection formula 1

[bodev]

Hi Gurus,

Environment: Crystal Reports 2008
Business Objects XI 3.1

I have 2 date parameters (?StartDate and ?EndDate) that take user input for Beginning and End dates. I want to change the date that the user inputs so that the date part remains the same but gets time stamp of 11:59:59 PM. I m trying to get this done in Record Selection Formula. I m able to change the Start Date by adding one day and then subtracting One Second from it, but cannot get the End Date to change. For the End Date, I m doing the same thing i.e. add one day to ?EndDate parameter using formula dateadd("d",1,{?EndDate}) and then using that formula in Record Selection Formula to subtract One Second as: dateadd("s",-1,@formula)
but somehow i cant get it to work.

Please suggest a solution.

Thanks.

 

[lbass]

If these are date type parameters, you could use a formula like this:

{table.datetime} in datetime({?StartDate},time(0,0,0)) to
datetime({?EndDate},time(23,59,59))

-LB

 

[bodev]

Hi lbass,

thanks for taking the time to reply to my post. I tried what you suggested, but the Record Selection Formula still does not take into account the time portion of the End Date parameter. I m testing this functionality on Northwind access database and i modified my record selection as: {Orders.OrderDate} in dateadd("s",-1,{@Add_1_day_fromDate}) to datetime({?toDate}, time("23:59:59pm"))
BUT, the Show SQL Query in Crystal still shows the where clause as:
WHERE (`Orders`.`OrderDate`>=#1996-09-02 23:59:59# AND `Orders`.`OrderDate`<#1997-07-02 00:00:00#)

Any idea what i m doing wrong?

Thanks.

 

[lbass]

But that IS correct, if you entered an end date of 7/1/1997 at the prompt. The SQL is showing that the datetime must be less than the 1997/7/2 which translates to up througth 1997/7/1 at 23:59:59 (one second before midnight). Midnight is considered the first time of a date.

If the end date is really 6/30/1997, then you should be entering that.

-LB

 

[bodev]

I sincerely apologize for not seeing this. You are right. Thanks for pointing it out.

Best Regards.