DATE_FORMAT() function in queries for reports Topic is solved

[capstefano]

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

[steven]

Hi capstefano,

If your SQL isn't working, check the debugger.

If your SQL has a problem, it will tell you there...

errors.png

Like this...

debug.png

Steven

[capstefano]

thanks, as usual, for your help Steven

[capstefano]

Solved.

Code: Select all

CONCAT('some_string', DATE_FORMAT('#hash_cookie#')) AS alias

is not parsed and the debug output is:

Code: Select all

SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect datetime value: '#diario_inizio#'
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'albagest2.___nu16716a55c011ee___' doesn't exist

BUT if the DATE_FORMAT() is nested in a CONCAT() AND in the concatenation function at least a regular data field exists, like

Code: Select all

CONCAT(table.field, ' ', DATE_FORMAT('#hash_cookie#'))

then the query is processed, fields appear in the report-building form, and no output is shown in nuDebug. The created report works as well.

I can't even imagine (and really don't mind about) the causes for this peculiar behavior, but maybe is of some interest for you. Bye!

[steven]

capstefano,

If you find an SQL statement that works but you're not sure why, change it so that it would create an error eg.

Change SELECT * FROM bob

to SELECT1 * FROM bob

It will create an error in the debugger (nuDebug).

That will give you the SQL of the statement that worked - even though you're not sure why.


Steven

[steven]

capstefano,

I have created a video for the debugger...

Using nuBuilder's debugger - nuDebug()

Steven

[capstefano]

useful tool indeed, thanks a lot Steven