Search Record between range Date

i have two column in my table where startDates and EndDates are stored (Start date - 2019-02-15)(EndDate - 2019-02-20) . so here when user will have 2 search box where if they put start date as '2019-12-17' they should see the relevant record who's range comes between the given date.
what i have tried is 

(@searchstartdate BETWEEN [startdate] AND [EndDate] or @searchstartdate = nulldate()) AND 

(@searchenddate BETWEEN [startdate] AND [EndDate] or @searchenddate = nulldate())


 and also i have tried 

@searchstartdate >= [startdate] and @searchstartdate <= [enddate] or @searchstartdate = nulldate() AND

@searchenddate >=[startdate] and @searchenddate <= [enddate] or @searchenddate = nulldate()



any suggestions ?

 

Vikas C wrote:

i have two column in my table where startDates and EndDates are stored (Start date - 2019-02-15)(EndDate - 2019-02-20) . so here when user will have 2 search box where if they put start date as '2019-12-17' they should see the relevant record who's range comes between the given date.
what i have tried is 

(@searchstartdate BETWEEN [startdate] AND [EndDate] or @searchstartdate = nulldate()) AND 

(@searchenddate BETWEEN [startdate] AND [EndDate] or @searchenddate = nulldate())


 and also i have tried 

@searchstartdate >= [startdate] and @searchstartdate <= [enddate] or @searchstartdate = nulldate() AND

@searchenddate >=[startdate] and @searchenddate <= [enddate] or @searchenddate = nulldate()



any suggestions ?

 

Hi Viskas,


Try:

(@searchstartdate = nulldate()

or

(@searchstartdate >= [startdate] and @searchstartdate <= [enddate]))

and

(@searchenddate = nulldate()

or

(@searchenddate >=[startdate] and @searchenddate <= [enddate]))


Kind regards,

Hugo

Hi Viskas,

If I understand well, you have the following scenarios:

  1. All Records will appear
  2. Will appear the records with start date >= search start date and no search end date defined
  3. Will appear records with end date <= search end date and no search start date defined
  4. Will appear records between the search start and end dates defined


If these are the scenarios, try the following:

SyntaxEditor Code Snippet

(@searchstartdate=NullDate()) and @searchenddate=NullDate())       

or

(@searchstartdate<>NullDate() and [startdate]>=@searchstartdate and @searchenddate=NullDate())

or

(@searchenddate<>NullDate() and [enddate]<=@searchenddate and @searchstartdate=NullDate())

or

(@searchstartdate<>NullDate() and @searchenddate<>@NullDate and [startdate]>=@searchstartdate and [enddate]<=@searchenddate)


Regards


Hi Viskas,

Unless they're defined as a FK, OutSystems entity attributes are never NULL. They default to a value depending on the data type - in case of Date is 1900/01/01. As so, if your querying an entity defined in OutSystems you should test for the platform's default null value for the given data type. Please check the example with a SQL node.



Cheers,

Tiago.