Crystal reports : record selection greater equals date & time AND less then date & time

Question

In crystal reports record selection, how to select records between date and time.

The table has separate fields for date and time.

For Example:
From date 24-July-2015 9:00 PM
To date 25-July-2015 9:00 AM

So i want to select records from 24th July 9:00 PM onwards to 25th July upto 9:00 AM

How can i do this ?

Answer 1

You can create a single parameter for both date&time..

Answer 2

I can think of 2 solutions..

1. Create a view that combines date and time field of database and create a single column. Use the view in reports and apply a record selection formula for that datetime field like

   databaseview.datetime>='24-July-2015 9:00 PM' and databaseview.datetime<'25-July-2015 9:00 AM'

2. Use date in record selection as

   database.date>='24-July-2015' and database.date<'25-July-2015'

Now in your report for every field apply a condition on time and display the report.. something like below.

If (database.time>='9:00 PM' and database.time<'9:00 AM')
then yourdisplayfield

Answer 3

Then

1.create a formula to add date & time as single field.. okkk

Formula: datetime(Date,Time)

2.Create a static Parameter type as DateTime and In the value option select the Allow range of value as true..

3.In the Select export choose the formula and parameter that you have created..

it will work..

-Ankur

Answer 4

Finally I was able to do it. Here is how

in record selection:

{@EndDate} in {?FromDate} to {?ToDate} and
{@StartDate} in {?FromDate} to {?ToDate} and
(if ({@StartDate} in {?FromDate} to {?FromDate}) then CTime({@StartTime}) >= CTime(ToText({?StartTime})) else if ({@EndDate} in {?ToDate} to {?ToDate}) then CTime({@EndTime}) <= CTime(ToText({?EndTime})) else true)

Thanks @Ankur for helping out :)