DATE_FORMAT() function in queries for reports
Posted: Mon Oct 21, 2024 1:28 pm
Hello everyone.
I'm preparing SQLs for several reports with a date filter criteria (like: WHERE table.date_field BETWEEN '#start_date#' AND '#end_date#') and I've noticed that the parser doesn't accept queries that are using the DATE_FORMAT() function (I'm using mariadb).
[I've used the DATE_FORMAT for the select obvously, NOT for the clauses]
Firstly I though it was because my DATE_FORMAT was included in a CONCAT, but then I verified that the formatted value broke the report creation process (with no tables shown when selecting the query from the lookup) even if it's an alone value.
Of course, every field was aliased.
I know that nubuilder has a built-in function for managing formats, but had localization issues with that, so I though that formatting values at the query level could be a good idea (entering a string like 'it_IT' in the function) and the query is working as expected in the regular SQL parser (like in phpmyadmin) but had no luck.
I also tried to create a php procedure from my query, but fields are not shown anyway in report editor.
What I'd like to know is:
Can you confirm that the nubuilder parser doesn't accept DATE_FORMAT() values?
Is there a viable way to localize the formats created with the format editor? Or a way to bypass my problem?
I'd like to obtain a value like DATE_FORMAT(table.date_fields, '%d %M %Y', 'it_IT')*
*(in some queries I have a database field, in some others I use an '#hash_cookie#' from a form object, of appropriate data type/structure)
EDIT: I have noticed that I actually HAVE some working queries with a DATE_FORMAT() function (moreover, inside a CONCAT())
so I have to somehow work around the SQLs, I suppose. I'll maybe try to write them from scratch (only a few), but I was wondering why the same queries run without a problem using phpmyadmin. If the nubuilder parser require any special condition than the server's it's not a big issue, in my opinion, but it would be an useful information to have.
By the way, it's probably just me messing up something while writing down the query.
Everything as usual
I'm preparing SQLs for several reports with a date filter criteria (like: WHERE table.date_field BETWEEN '#start_date#' AND '#end_date#') and I've noticed that the parser doesn't accept queries that are using the DATE_FORMAT() function (I'm using mariadb).
[I've used the DATE_FORMAT for the select obvously, NOT for the clauses]
Firstly I though it was because my DATE_FORMAT was included in a CONCAT, but then I verified that the formatted value broke the report creation process (with no tables shown when selecting the query from the lookup) even if it's an alone value.
Of course, every field was aliased.
I know that nubuilder has a built-in function for managing formats, but had localization issues with that, so I though that formatting values at the query level could be a good idea (entering a string like 'it_IT' in the function) and the query is working as expected in the regular SQL parser (like in phpmyadmin) but had no luck.
I also tried to create a php procedure from my query, but fields are not shown anyway in report editor.
What I'd like to know is:
Can you confirm that the nubuilder parser doesn't accept DATE_FORMAT() values?
Is there a viable way to localize the formats created with the format editor? Or a way to bypass my problem?
I'd like to obtain a value like DATE_FORMAT(table.date_fields, '%d %M %Y', 'it_IT')*
*(in some queries I have a database field, in some others I use an '#hash_cookie#' from a form object, of appropriate data type/structure)
EDIT: I have noticed that I actually HAVE some working queries with a DATE_FORMAT() function (moreover, inside a CONCAT())
so I have to somehow work around the SQLs, I suppose. I'll maybe try to write them from scratch (only a few), but I was wondering why the same queries run without a problem using phpmyadmin. If the nubuilder parser require any special condition than the server's it's not a big issue, in my opinion, but it would be an useful information to have.By the way, it's probably just me messing up something while writing down the query.
Everything as usual