nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Katerina Kovriga {Stiltsoft}

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:

Wed 8-1.png

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:

Wed 8-2.png

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

Wed 8-3.png

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):

Wed 8-4.png

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.

All comments

Katerina Kovriga {Stiltsoft}

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:

Wed 8-1.png

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:

Wed 8-2.png

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

Wed 8-3.png

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):

Wed 8-4.png

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:

Thu 3-1.png

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

Thu 3-2.png

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):

Wed 9-1.png

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

server

confluence-server