Hi @Bhushan Lahane,

To make your query more efficient and to stop repeating the use of GETDATE(), you can create a variable for it just once and use it every time you need it in your OutSystems query. The error you saw, "DATETIME is not a recognized CURSOR option," means that SQL requires variables to be declared and used properly. 

SQL -

DECLARE @CurrentDate DATETIME; 

SET @CurrentDate = GETDATE();

 SELECT  *  FROM {TaxRuleVersion}

 WHERE {TaxRuleVersion}.[IsLatestRevision] = 1  

AND (   

 (@IsActiveVersion = 'True' 

AND {TaxRuleVersion}.[EffectiveFrom] <= @CurrentDate 

AND {TaxRuleVersion}.[EffectiveTo] >= @CurrentDate)    

OR    (@IsFutureVersion = 'True' AND {TaxRuleVersion}.[EffectiveFrom] > @CurrentDate)    

OR    (@IsEndedVersion = 'True' AND {TaxRuleVersion}.[EffectiveTo] < @CurrentDate)  ) 

• It sets the date once and uses it many times, which is better for performance.
• It stops the system from recalculating GETDATE() every time it's compared, which cuts down on extra work.
• You can use this method in OutSystems by putting the SQL inside an 'Advanced SQL' block.

Thanks,

Vipin Yadav

Hi, @Bhushan Lahane 

Usually it's doesn't work like that both CurrDateTime() and GetDate() will be same, i don't know why your results are different.

Well you already have Variable to SQL called CurrentDate AS DateTime, So you can directly set a value inside a SQL even if you send value from logic that value will be override in SQL.

SET @CurrDateTime = GETDATE();

SELECT {TaxRuleVersion}.*

FROM {TaxRuleVersion}

WHERE   

         {TaxRuleVersion}.[IsLatestRevision] = 1
         AND ((@IsActiveVersion = 'True'
         AND ({TaxRuleVersion}.[EffectiveFrom] <=  @CurrDateTime
         AND {TaxRuleVersion}.[EffectiveTo] >= @CurrDateTime))   
 OR (@IsFutureVersion = 'True' AND {TaxRuleVersion}.[EffectiveFrom] > @CurrDateTime) OR (@IsEndedVersion = 'True' AND {TaxRuleVersion}.[EffectiveTo] < @CurrDateTime))

Hi,

Retrieve the GETDATE() value, assign it to a local variable, and then use the variable in the SQL query.

 CurrentDate  = GETDATE();

WHERE     {TaxRuleVersion}.[IsLatestRevision] = 1    AND (        (IsActiveVersion = 'True'          AND ({TaxRuleVersion}.[EffectiveFrom] <= CurrentDate AND {TaxRuleVersion}.[EffectiveTo] >= CurrentDate))         OR (IsFutureVersion = 'True'             AND {TaxRuleVersion}.[EffectiveFrom] > CurrentDate)         OR (IsEndedVersion = 'True'             AND {TaxRuleVersion}.[EffectiveTo] < CurrentDate)    )