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; } } }

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

Accepted answers

Stiltsoft support

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?

All comments

Stiltsoft support

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 (//) ?

Welcome!

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

Quick Links

Find more posts tagged with

confluence

filter

data-center

table

table-filter

table-filter-and-charts

confluence-data-center

table-transformer

table-filter-charts-spreadsheets