Confluence default '//' date format not aligned with sql

[MudCo]

Hi Team, I have a table that contains data that I need to segregate into 4 different FY's (FY25Q1 to FY25Q4). The date format used in my column is Confluence's standard '//'.  The code I have below gives me results where all the results in the new column (END AS..) is text 'BEYOND FY25'. I am not sure if the code is incorrect or if Confluence is unable to recognise the date format used in my code vs the format in my table. 

SELECT *,

CASE

WHEN T1.'Date Contract will Expire' >= '01 Oct 2024' AND T1.'Date Contract will Expire' <= '31 Dec 2024' THEN "FY25Q1"

WHEN T1.'Date Contract will Expire' >= '01 Jan 2025' AND T1.'Date Contract will Expire' <= '30 Mar 2025' THEN "FY25Q2"

WHEN T1.'Date Contract will Expire' >= '01 Apr 2025' AND T1.'Date Contract will Expire' <= '30 Jun 2025' THEN "FY25Q3"

WHEN T1.'Date Contract will Expire' >= '01 Jul 2025' AND T1.'Date Contract will Expire' <= '30 Sep 2025' THEN "FY25Q4"

ELSE "BEYOND FY25"

END AS 'FY25 & BEYOND Contracts'

FROM T1

[Stiltsoft support · Accepted solution]

Hi @MudCo ,

Please define what 3rd party app you are using cause there is no any SQL in Confluence out-of-the-box.

Is it our Table Filter, Charts & Spreadsheets for Confluence app and its Table Transformer macro or smth else?

[MudCo]

Hi, apologies. I missed to mention this is inside a table transformer I have wrapped my table in.

[Stiltsoft support]

Then check that the date format in the Table Transformer macro settings corresponds to the format that is used in your source table.

You've mentioned that you use the standard // datepicker but it is a standard format for you and not for all Confluence instances (it depends, for example, on the language used). Besides, if you used the macro with a different set of settings previously, it may remember them and the date format may differ.

Then check the SQL query: as I see, you refer to your dates as '01 Oct 2024'.

The macro is based on the AlaSQL query, so ' ' and " " differ. The ' ' are used when you call out your column names and " " are used when you refer to strings. So, the "01 Oct 2024" option will be correct.

[MudCo]

Hi there, spologies to respond back late.

I tried with both ' '  and '' '' and the results was, the whole column, irrespective of the dates gave the output BEYOND FY25 for all rows. Also, when you said " " would be more apt, wouldn't that be used for a text string and not the confluence date (//) ?

[MudCo]

Adding to it

'01 Oct 2024' AND T1.'Date Contract will Expire' <= '31 Dec 2024' THEN "FY25Q1" 

where my date range is between the given criteria and in tables the date looks like the other screenshot attached. Regarding location, I checked my system and I am attaching the screenshots for the same. 

[Stiltsoft support]

As I've said before, ' '  are used when you call out column names. When you refer to strings, you need to use " ": 

"01 Oct 2024" AND T1.'Date Contract will Expire' <= "31 Dec 2024" THEN "FY25Q1" 

And again make sure that you have a proper date format inside the Table Transformer macro settings: the "01 Oct 2024" corresponds to the "dd M yy".

[MudCo]

perfect! I checked my settings and it was not in "dd M yy" form. And I changed the ' ' to 

" ". Worked perfectly. Thanks so much for your help!