-1

I am not a SQL user, it happens that I need to know what this type of SQL code means, can you help me please translating it to me?

CASE 
    WHEN DATEDIFF(to_date(B.DT_CAREPACK_END),
                  LAST_DAY(date_sub(add_months(current_date, 3), 20))) > 0
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
omar hmissi
  • 3
  • 1
  • 2
    What DBMS is this? – Thorsten Kettner Sep 04 '22 at 13:33
  • The `DATE_SUB` looks strange. Subtract 20 from a date? 20 what? Days? Weeks? Years? `TO_DATE` seems to want to convert `dt_carepack_end` to a date. But why is this column called `dt_` when it is not a date already? And if we assume a string, then where is the conversion format? How is the DBMS supposed to know what date format that string contains? Maybe it relies on some sessions settings there, but this is not how it should be done. And without seeing the docs for `DATEDIFF`, we don't know whether it returns a positive value when the first date is smaller or when it is greater. – Thorsten Kettner Sep 04 '22 at 13:42

1 Answers1

1

CASE statements let you introduce some conditional logic.

A full CASE sttatement would have a syntax like:

CASE 
     WHEN some logic is true THEN x
     WHEN some other logic is true THEN y
     ELSE THEN z
     END as column_title

In your example, it doesn't look like you've provided the full statement as their is no END keyword.

Basically, this logic is checking when the difference between two dates (date-x and date-y) is positive or not. DATE_DIFF looks at the different between a start date (date-x) and an end date (date-y).

If date-x, the start date, is before date-y, the end date, then the result is positive. If the start date is after the end date, then the result is negative.

  • date-x is a date representation of the column DT_CAREPACK_END
  • date-y is taking the current_date, adding on 3 months (e.g. 4th September becomes 4th December), is is then subtracting 20 units (presumably days) and then setting that date to the last date of that month.

So, imagine DT_CAREPACK_END (presumably a date when something ends) is in the future and is 2022-10-02.

The inner logic here will take the current date (2022-09-04) and add 3 months to that date, making it 2022-12-04. Then, we are subtracting 20 days which is 2022-11-14. Then, we find the last day in that month, which would be 2022-11-30.

Finally, we look at the difference between 2022-10-02 (start date) and 2022-11-30 (end date). If that is a positive number, then the logic is satisfied. In this case, 2nd October is before 30th November, resulting in a positive logic and therefore the case logic is satisfied.

If the DT_CAREPACK_END is before the current_date logic, then it would be negative.

*N.B. I thought that date_add, date_sub and date_diff functions needed an interval unit to be explicitly stated (e.g. INTERVAL 20 DAY). I'm guessing the default here is days but that's an assumption on my part. I'm working in good-faith that the code snip is syntatically correct. *

Resources:-

Add Months: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions004.htm

Date Sub: https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/date-and-time-functions.html#function_date-add

Last Day: https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/date-and-time-functions.html#function_last-day

lummers
  • 689
  • 3
  • 8
  • 17