How the below code works

Question

Hi

How the below code works . It is used in Sap b1

SELECT 'True' FROM DUMMY WHERE

Add_days(Last_day(Add_days(Last_day(ADD_months(cURRENT_DATE,-2)),1) ),5) < cURRENT_DATE

AND TO_DATE('Total Selected Terms:','DD-MM-YYYY')  > Last_day(Add_days(Last_day(ADD_months(cURRENT_DATE,-3)),1))

and Monthname(TO_DATE('Total Selected Terms:','DD-MM-YYYY')) <> Monthname(cURRENT_DATE)

Thanks

Muhammad Imran Ansari · Answer

Hello Ramco This SQL query is used in SAP Business One (SAP B1) and is designed to evaluate certain date-based conditions. Let’s break it down step by step to understand how it works: Query Breakdown: SELECT 'True' FROM DUMMY WHERE Add_days(Last_day(Add_days(Last_day(ADD_months(cURRENT_DATE-2))1) )5) Last_day(Add_days(Last_day(ADD_months(cURRENT_DATE-3))1)) AND Monthname(TO_DATE('Total Selected Terms:''DD-MM-YYYY')) Monthname(cURRENT_DATE); DUMMY Table: In SAP B1 DUMMY is a system table that contains a single row and is often used for calculations or testing conditions without needing actual data from a table. Date Functions Used: CURRENT_DATE: Returns the current date. ADD_MONTHS(date n): Adds or subtracts n months from the given date. LAST_DAY(date): Returns the last day of the month for the given date. ADD_DAYS(date n): Adds or subtracts n days from the given date. TO_DATE(string format): Converts a string to a date based on the specified format. MONTHNAME(date): Returns the name of the month for the given date. Condition 1: Add_days(Last_day(Add_days(Last_day(ADD_months(cURRENT_DATE-2))1) )5) Step-by-Step Evaluation: ADD_MONTHS(CURRENT_DATE -2): Subtracts 2 months from the current date. Example: If today is 2023-10-15 this results in 2023-08-15. LAST_DAY(ADD_MONTHS(CURRENT_DATE -2)): Finds the last day of the resulting month. Example: 2023-08-31. ADD_DAYS(LAST_DAY(ADD_MONTHS(CURRENT_DATE -2)) 1): Adds 1 day to the last day of the month. Example: 2023-09-01. LAST_DAY(ADD_DAYS(LAST_DAY(ADD_MONTHS(CURRENT_DATE -2)) 1)): Finds the last day of the new month. Example: 2023-09-30. ADD_DAYS(LAST_DAY(ADD_DAYS(LAST_DAY(ADD_MONTHS(CURRENT_DATE -2)) 1)) 5): Adds 5 days to the last day of the new month. Example: 2023-10-05. Compare the result with CURRENT_DATE: Example: 2023-10-05 Condition 2: TO_DATE('Total Selected Terms:''DD-MM-YYYY') > Last_day(Add_days(Last_day(ADD_months(cURRENT_DATE-3))1)) Step-by-Step Evaluation: ADD_MONTHS(CURRENT_DATE -3): Subtracts 3 months from the current date. Example: If today is 2023-10-15 this results in 2023-07-15. LAST_DAY(ADD_MONTHS(CURRENT_DATE -3)): Finds the last day of the resulting month. Example: 2023-07-31. ADD_DAYS(LAST_DAY(ADD_MONTHS(CURRENT_DATE -3)) 1): Adds 1 day to the last day of the month. Example: 2023-08-01. LAST_DAY(ADD_DAYS(LAST_DAY(ADD_MONTHS(CURRENT_DATE -3)) 1)): Finds the last day of the new month. Example: 2023-08-31. Compare TO_DATE('Total Selected Terms:' 'DD-MM-YYYY') with the result: Example: If 'Total Selected Terms:' is 2023-09-15 then 2023-09-15 > 2023-08-31 → True. Condition 3: Monthname(TO_DATE('Total Selected Terms:''DD-MM-YYYY')) Monthname(cURRENT_DATE) Step-by-Step Evaluation: TO_DATE('Total Selected Terms:' 'DD-MM-YYYY'): Converts the string to a date. Example: If 'Total Selected Terms:' is 2023-09-15 this results in 2023-09-15. MONTHNAME(TO_DATE('Total Selected Terms:' 'DD-MM-YYYY')): Returns the month name of the date. Example: September. MONTHNAME(CURRENT_DATE): Returns the month name of the current date. Example: If today is 2023-10-15 this results in October. Compare the two month names: Example: September October → True. Final Query Logic: The query returns 'True' if all three conditions are satisfied: The calculated date (2023-10-05) is less than the current date (2023-10-15). The converted date from 'Total Selected Terms:' (2023-09-15) is greater than the calculated date (2023-08-31). The month of 'Total Selected Terms:' (September) is not the same as the current month (October). Good Luck!