Description: Firebird's DATEADD function can't be executed in Design View, only can be executed in SQL direct, so can't be used in editable queries, because SQL Direct don't generate editable queries Steps to Reproduce: 1. Create a table with at least one date field 2. Create a query in SQL View similar to this: SELECT "ID", "F1", DATEADD( DAY, 5, "F1" ) "F1+5" FROM "T1" 3. Execute the query in with *Run SQL command directly* activated 4. Try to execute the query with *Run SQL command directly* desactivated Actual Results: In 3. It runs, but the result query is not editable In 4. It don't run Expected Results: In 4. It Should run and produce an editable query Reproducible: Always User Profile Reset: No Additional Info: Version: 7.6.0.1 (X86_64) / LibreOffice Community Build ID: 776eaf34564cbf3f034a0ba1fd1d5c32ff9ccf1c CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win Locale: es-ES (es_ES); UI: es-ES Calc: CL threaded
Created attachment 188659 [details] Simple database to test
+1, as it is a standard SQL function. The same with DATEDIFF function I think not so simple to implement, in design view. Maybe an intermediate way, allowing to use it without a detailed control on them. Allowing also other standard SQL functions having the issue.
Seems it has also be fixed while fixing https://bugs.documentfoundation.org/show_bug.cgi?id=104918 Made a short test with adding some days to a date and some hours to a timestamp and it will work without setting direct SQL.
It makes sense, I was so obsessed with solving it differently than in DATEDIFF that I didn't realize that the solution was valid for DATEADD as well.
(In reply to Robert Großkopf from comment #3) > Seems it has also be fixed while fixing > https://bugs.documentfoundation.org/show_bug.cgi?id=104918 > > Made a short test with adding some days to a date and some hours to a > timestamp and it will work without setting direct SQL. (In reply to jcsanz from comment #4) > It makes sense, I was so obsessed with solving it differently than in > DATEDIFF that I didn't realize that the solution was valid for DATEADD as > well. Mmm! Reviewing everything again, it seems to me that it should not work. For the DATEADD function to work it needs to be added to the parser, that is done in my patch https://gerrit.libreoffice.org/c/core/+/155071 which has not yet been reviewed and approved. Without that patch, DATEADD was not working for me after adding DATEDIFF, it seems to me that some strange behavior is occurring... Let me put it back unresolved and we will come back in a few days.
(In reply to Robert Großkopf from comment #3) > Seems it has also be fixed while fixing > https://bugs.documentfoundation.org/show_bug.cgi?id=104918 > > Made a short test with adding some days to a date and some hours to a > timestamp and it will work without setting direct SQL. Ok(In reply to jcsanz from comment #5) > > Let me put it back unresolved and we will come back in a few days. I've tested again it it seems all works ok, so I mark this as Resolved again. I must have done something wrong when I checked the DATEADD function after adding DATEDIFF and it didn't work, although logically it should have worked. Thanks Robert to test this too.
As Robert said it works after DATEDIFF was added. Tested with: Version: 7.6.1.0.0+ (X86_64) / LibreOffice Community Build ID: 87e0ac2b92f7a7aac16bf66d2379688238496a75 CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win Locale: es-ES (es_ES); UI: es-ES Calc: CL threaded ------------ Version: 24.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 5fecd865303b3f0a2eeb0b9466d2bcf23cfce068 CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win Locale: es-ES (es_ES); UI: es-ES Calc: CL threaded
Thanks Juan Carlos
jucasaca committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/ccf30787a701860d19941a1d36ad3d1db2fb2e06 tdf#156534 Add Firebird's DATEADD function to SQL parser It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
jucasaca committed a patch related to this issue. It has been pushed to "libreoffice-7-6": https://git.libreoffice.org/core/commit/60e08a6cfda00a52088500b695b0adc1fe99b768 tdf#156534 Add Firebird's DATEADD function to SQL parser It will be available in 7.6.1. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.