Error with DATE_FORMAT

Get Help
1

Hello guys,

I have a subtable as follows :

id ActualEndDate CreatedAt PlanType Price MonthlyPrice
ABC 2023-06-09 2022-06-10 Annual 110 11
CFR 2022-08-11 2022-06-12 Mensual 12 12
CFR 2022-08-15 2022-07-15 Mensual 12 12

And I’m trying to have this results :

Month MRR
2022-06 23
2022-07 35
2022-08 35

When i try this query :

SELECT DATE_FORMAT("Period"."Month", '%Y-%m') "Month",
       COALESCE(SUM("MonthlyPrice"), 0) "MRR"
FROM (
              SELECT DATE_FORMAT(NOW() - INTERVAL 6 MONTH,'%Y-%m-01') AS "Month"
    UNION ALL SELECT DATE_FORMAT(NOW() - INTERVAL 5 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() - INTERVAL 4 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() - INTERVAL 3 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() - INTERVAL 2 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 0 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 1 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 2 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 3 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 4 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 5 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 6 MONTH,'%Y-%m-01')) AS "Period"
LEFT JOIN {{#165}} AS "subscription" ON "Period"."Month" 
                           BETWEEN DATE_FORMAT("subscription"."CreatedAt",'%Y-%m-01') 
                               AND LAST_DAY("subscription"."ActualEndDate") 
GROUP BY "Period"."Month"

I get this error message so I guess something is not well written in my date_format query for metabase but what is it :

ERROR: syntax error at or near "6" Position: 15

2

Hi @ChristopheMrn
Metabase sends the query to your database after substitution variables.
I don't know which database type you're querying, but if you take your query (without variables) then it will also fail if you run it on your database directly.

3

Hello @flamber
I did not completely got what you meant.
I should try directly my query on the database or my query is just wrong in the way it’s written ?
I’m querying a postgresql.

Thank you.

4

@ChristopheMrn Postgres uses to_char() instead of date_format()
https://www.postgresql.org/docs/current/functions-formatting.html
So it should be TO_CHAR("subscription"."CreatedAt",'YYYY-MM-01')