SQL date comparison

[Gohil Jay]

Dear,
I have a need to compare dates of a data with an idle date, but for some dates it makes the comparison and able to provide appropriate result but for some it doesn't.
-Format of the date is identical throughout.

-- WHEN FORMATDATE(DATE(FORMATEDATE(T1.'ECU'))) < FORMATDATE("13.12.2021") THEN FORMATDATE("13.12.2021")
ELSE T1.'ECU'

END AS 'ECU-NEW' 

Example

      ECU              ECU-NEW

1) 18.01.2021     18.01.2021 (Condition didn't fill, the result should be 13.12.2021.)

2) 12.01.2021     13.12.2021

3) 18.01.2022     18.01.2022

4) 10.12.2021     13.12.2021

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @Gohil Jay ,

It seems that the Table Transformer macro doesn't understand the FORMATDATE("13.12.2021") value, so the comparison works incorrectly.

Please try to use the following workaround:

In the internal Table Transformer macro we are making sure that our dates have the right format and that the comparison date is fully matching them:

SELECT FORMATDATE(T1.'ECU') AS 'ECU',
FORMATDATE("13.12.2021") AS 'Comparison Date'
FROM T*

Now we go to the external Table Transformer and compare our two columns (and don't list the "Comparison Date" column to be shown in the output):

SELECT
CASE WHEN T1.'ECU' < T1.'Comparison Date' THEN FORMATDATE("13.12.2021")
ELSE T1.'ECU'
END AS 'ECU-NEW'
FROM T*

Now everything seems to be working fine.

[Katerina Kovriga _Stiltsoft_]

Hi @Gohil Jay ,

I've also consulted with our developers, they told me that the FORMATDATE() option returns strings and not the real dates.

So you don't need the internal Table Transformer to create another column with dates, you may use the simplified query from the external Table Transformer macro as it is:

SELECT
CASE WHEN T1.'ECU' < "13.12.2021"
THEN FORMATDATE("13.12.2021")
ELSE T1.'ECU'
END
AS 'ECU-NEW'
FROM T*

Hope this helps your case.

[Gohil Jay]

Hi @Katerina Kovriga _Stiltsoft_ 

I tried that solution but unfortunately the result is not satisfactory.


A new code works somehow but not in all pages,

FORMATDATE(DATE(FORMATDATE(T1.ECU))) < FORMATDATE(DATE(FORMATDATE("01.01.2021")))

I also spoke with other programmers in my contact, they gave their best try but sometime Confluence SQL gives proper outcome and some time it doesn't.

Thanks,

[Katerina Kovriga _Stiltsoft_]

The two variants of code that I've given should work correctly.

The "FORMATDATE(DATE(FORMATDATE(T1.ECU)))" is not a proper solution, it shouldn't be working correctly: you convert the date into string, then into date and again into string. And as I've shown before you can compare dates directly.

Check if your Table Transformer macro is set for the proper date format itself (dd.mm.yy. stands for 23.03.2022): 

Then check the version of the app your are using - the current one is 8.2.0, if you have an older one, please update.

If the issue persists, raise a support ticket (you may attach the link to this thread as well) - attach the Page Storage Format (upper right corner -> menu ... -> View Storage Format). If you don't see the option, ask your administrator to do it for you. Our support will recreate exactly your source table, your Table Transformer with the SQL query and look into the issue.