• From: Canada, Yukon
  • Registered: 09/27/2014
  • Posts: 121

Topic: error message : Incorrect date value: '' for column

Hello Gurus,

I have been spinning my wheels on this for a while and hoping someone can point me in the right direction.

Some time ago I made a Purchase order and received it, then sent a customer invoice for the received goods.  I needed to do this because the customer needed the invoice before I could get an invoice from the supplier.

Today, I am trying to convert the purchase order delivery into a supplier invoice.  I am on the supplier invoice page, have added the inventory items, filled in dates and reference and such, but consistently get this error:

DATABASE ERROR : cannot add journal entry
error code : 1292
error message : Incorrect date value: '' for column `fa_computerisms_ca`.`journal`.`event_date` at row 1

I have tried a variety of dates, from well before everything happened to next year, nothing seems to change the empty date.

I can't really find any thing in the forums that match this problem, but nonetheless I have tried, and failed, to hack the code in a few places based on what similarities I did find.

FWIW, I had another Purchase order that had not been received.  I was able to receive those times and create a supplier invoice for those items.  However, I did not send a sales invoice to a customer for those items prior, so perhaps that is the difference.

Any suggestions how I might get this invoice entered?

bobloblian's Website

  • From: Canada, Yukon
  • Registered: 09/27/2014
  • Posts: 121

Re: error message : Incorrect date value: '' for column

something else that may be relevant:

after I had received the items, I had to put the wrong date, so I voided the original purchase order delivery and made a new one with the correct date.  Perhaps now that I am trying to invoice, it is referencing the date on the voided delivery, and coming up with an empty date?  I can see two deliveries on the Purchase Order...

bobloblian's Website

  • From: Canada, Yukon
  • Registered: 09/27/2014
  • Posts: 121

Re: error message : Incorrect date value: '' for column

I got it in my head that I can make a new direct invoice, so I have the items received again, and this time paid for.

Now, in trying to undo the already received items; If I try to void the Purchase Order Deliver I used to initially receive the items, I get the same Mysql error:

DATABASE ERROR : cannot add journal entry
error code : 1292
error message : Incorrect date value: '' for column `fa_computerisms_ca`.`journal`.`event_date` at row 1

If I go under supplier invoices and try to remove the items there, I also get the same error.  I can however remove the shipping charges that were on the purchase order from this screen.

If I try to cancel the order, I get an expected error that I cannot do that because the items have already been received.

And there doesn't appear to be a way to void a purchase order.

So thinking I will use an inventory adjustment to remove the items.  Since there is actually only one invoice, the money should still be tracked accurately, I think?

wish I knew what I did to make this happen, so that it can be prevented from happening again...

bobloblian's Website

  • From: Canada, Yukon
  • Registered: 09/27/2014
  • Posts: 121

Re: error message : Incorrect date value: '' for column

*sigh*

inventory adjustment gives same error, but one more line:

Undefined property: line_item::$qty in file: /var/www/fa.computerisms.ca/inventory/includes/db/items_adjust_db.inc at line 36
DATABASE ERROR : cannot add journal entry
error code : 1292
error message : Incorrect date value: '' for column `fa_computerisms_ca`.`journal`.`event_date` at row 1


quantity I have entered in the adjustment items section is -4. 

dunno what else to do.  Sorry I broke your program wink

bobloblian's Website

  • From: USA
  • Registered: 07/16/2017
  • Posts: 276

Re: error message : Incorrect date value: '' for column

You found at least one bug: Line 36-38 in items_adjust_db.inc

if ($SysPrefs->loc_notification() == 1 && $line_item->qty < 0)
        {
            $chg = $line; $chg->qty = -$chg->qty;   // calculate_reorder_level expect positive qty

The error message is correct.  qty is indeed not a member of class line_item.   (I think the coder meant to say $line_item->quantity.)

But even if the coder did mean quantity, $line isn't defined either.  So I doubt that this code was ever tested and would take some investigation to figure out what really is supposed to happen.

So why is the problem only plaguing you?  Well, maybe you are the only one with $SysPrefs->loc_notification() turned on?  Or maybe you just have an "advanced" version of php that doesn't just blithely ignore obvious language semantic problems?  Or maybe it just isn't your lucky day.

  • From: USA
  • Registered: 07/16/2017
  • Posts: 276

Re: error message : Incorrect date value: '' for column

As to the null date problem, there is likely another FA bug, perhaps related to the voiding.  What I like to do when I encounter these kinds of things is try to recreate the steps on a dummy company and see if I can get the problem to happen again.   If you can't reproduce the problem, then it is unlikely that anyone will figure it out.    Your next recourse would be to void the purchase order and recreate it all from scratch.   If the problem happens again, then you have figured out how to reproduce the problem,and then it should be much easier to find.

7 Reply by poncho1234 (edited by poncho1234 06/26/2019 11:26:36 pm)

  • From: Colombia
  • Registered: 12/29/2014
  • Posts: 627

Re: error message : Incorrect date value: '' for column

To add to Braath's advice in post #6

Do a backup, look in the 'journal' table, look at the 6th column(event_date) see if any row is 'different' to the other dates, cross check against the 'reference' column(4th column) Different could be date reversed, abnormal year, month, day. You'll notice all dates are in order looking down, so one date may standout from the others?

Note as all your efforts have resulted in 'DATABASE ERROR : cannot add journal entry' there may not be one, but erroneous event_date may be there?

The FrontAccounting Wiki(Manual, examples, tips, setup info, links to accounting sites, etc) https://frontaccounting.com/fawiki/
  • From: Canada, Yukon
  • Registered: 09/27/2014
  • Posts: 121

Re: error message : Incorrect date value: '' for column

Hi Guys, thank you both very much for your reply.

Regarding comment #5, I would suspect this is plaguing me because of the original problem.  given that I have used inventory adjustments before without issue, and it happens to be only this item that is the problem, and this item is having problems elsewhere, then this items seems to be the common denominator.  My intuition tells me it is because I generated a customer invoice on the received items before I entered a supplier invoice to pay for them, but I haven't taken the time to devise a test to prove the hypothesis.

Regarding comment #6; sadly the price of being busier with business is less time to devote to these projects, however, I will endeavour at some point to reproduce this issue if I can.  Also, I did try to void the purchase order, but it doesn't seem to be an option in the list of things I can void.  I am also worried that since I sold those items to a customer already, I might make things worse by voiding the PO of the items I sold.  Again in this case I need to export the existing system to a sandbox so I can mess with it, but right now the big priority is to get invoices out the door and money in the door, so I can go make more money.

Regarding post #7; yes, I had already looked through that table.  As far as date formats go every thing looks to be in order, format is yyyy-mm-dd for all entries, though there are a smattering of 0000-00-00 entries here and there.  I did start tracing the trans_no to see if I could attach those 0000-00-00 numbers to this item in some way, but didn't get far.  I also tried hard-coding dates in the code where I found a $date_ variable in a few different files, in the hopes I could just get the program to accept the supplier invoice.  I was also in the process of getting mysql debugging going so I could try to find the exact query that was failing, but phone keeps ringing and haven't had time to finish that process yet. 

I will see if I can put some more time to this over the weekend or next week...

bobloblian's Website

9 Reply by ichtus (edited by ichtus 07/16/2019 07:20:15 am)

  • From: Indonesia, Surabaya
  • Registered: 01/08/2012
  • Posts: 36

Re: error message : Incorrect date value: '' for column

facing this problem, happen in 1 database that have long list item that need to invoicing,
error log only shown:

[16-Jul-2019 15:45:08 Asia/Dili] <b>DATABASE ERROR :</b> cannot add journal entry<br>error code : 1292<br>error message : Incorrect date value: '' for column 'event_date' at row 1<br><br><br>

FA 2.4.7
PHP 7.2

any luck?

ps:
sucess to submit enter invoice, but dont know what happen, also change ini php.ini max_input_vars to raise more than 1000.
and found any suspicious in tmp/error.log
        $debug                  = 1;    // show sql on database errors

        $show_sql               = 1;    // show all sql queries in page footer for debugging purposes
        $go_debug               = 1;    // set to 1 for basic debugging, or 2 to see also backtrace after failure.
        $pdf_debug              = 1;    // display pdf source instead reports for debugging when $go_debug!=0
        // set $sql_trail to 1 only if you want to perform bugtracking sql trail
        // Warning: this produces huge amount of data in sql_trail table.
        // Don't forget switch the option off and flush the table manually after
        // trail, or your future backup files are overloaded with unneeded data.
        //
        $sql_trail              = 1; // save all sql queries in sql_trail

ichtus's Website

  • From: USA
  • Registered: 07/16/2017
  • Posts: 276

Re: error message : Incorrect date value: '' for column

Because you are running FA 2.4.7, I'm guessing the issue is a bug that was unmasked by the introduction of STRICT_ALL_TABLES.  In my installation, I am keeping this off until it has been tested for a few years.

+++ b/core/includes/db/connect_db_mysqli.inc
@@ -10,7 +10,7 @@
     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
 define('DB_DUPLICATE_ERROR', 1062);
-define('SQL_MODE', 'STRICT_ALL_TABLES'); // prevents SQL injection with silent field content truncation
+define('SQL_MODE', ''); // well tested
  • From: Indonesia, Surabaya
  • Registered: 01/08/2012
  • Posts: 36

Re: error message : Incorrect date value: '' for column

Hi,

how to temporary revert for STRICT_ALL_TABLES to posting transaction, we have problem in the new database and want to have adjustment stok, but experiencing same problem

[03-Sep-2019 16:17:18 Asia/Dili] <b>DATABASE ERROR :</b> cannot add journal entry<br>error code : 1292<br>error message : Incorrect date value: '' for column `accounting_visimitra`.`48_journal`.`event_date` at row 1<br><br><br>

Braath Waate wrote:

Because you are running FA 2.4.7, I'm guessing the issue is a bug that was unmasked by the introduction of STRICT_ALL_TABLES.  In my installation, I am keeping this off until it has been tested for a few years.

+++ b/core/includes/db/connect_db_mysqli.inc
@@ -10,7 +10,7 @@
     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
 define('DB_DUPLICATE_ERROR', 1062);
-define('SQL_MODE', 'STRICT_ALL_TABLES'); // prevents SQL injection with silent field content truncation
+define('SQL_MODE', ''); // well tested

ichtus's Website

  • From: Indonesia, Surabaya
  • Registered: 01/08/2012
  • Posts: 36

Re: error message : Incorrect date value: '' for column

details problem:

FA 2.4.7
php 7.2
mariadb 10.4.7

screenshot with debug and show sql on
Undefined property: line_item::$qty in file: /PATH/inventory/includes/db/items_adjust_db.inc at line 36
DATABASE ERROR : cannot add journal entry
error code : 1292
error message : Incorrect date value: '' for column `accounting_visimitra`.`46_journal`.`event_date` at row 1
sql that failed was : INSERT INTO 46_journal( `type`,`trans_no`, `amount`, `currency`, `rate`, `reference`, `source_ref`, `tran_date`, `event_date`, `doc_date`) VALUES('0','34','-0.01','IDR','1','VMK-1907-012','','2019-07-31','','')

https://www.dropbox.com/s/o4ujaaf8sq3f8pg/Screenshot%202019-09-03%20at%2015.23.29.png?dl=0

items 725184114885 history is adjust in 30 July with +1 qty
https://www.dropbox.com/s/2wtbjbybc8ok65z/Screenshot%202019-09-03%20at%2015.11.43.png?dl=0

MariaDB [accounting_visimitra]> select * from 46_journal;
+------+----------+------------+----------------------------+------------+------------+------------+----------+--------------------+------+
| type | trans_no | tran_date  | reference                  | source_ref | event_date | doc_date   | currency | amount             | rate |
+------+----------+------------+----------------------------+------------+------------+------------+----------+--------------------+------+
|    0 |        1 | 2019-08-01 | VMK-1908-001               |            | 2019-08-01 | 2019-08-01 | IDR      |                  0 |    1 |
|    0 |        2 | 2019-07-31 | VMK-1907-001               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |        3 | 2019-07-31 | VMK-1907-002               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |        4 | 2019-07-31 | VMK-1907-003               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |        5 | 2019-07-31 | VMK-1907-004               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |        6 | 2019-07-31 | VMK-1907-001               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |        7 | 2019-07-31 | VMK-1907-003               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |        8 | 2019-07-31 | VMK-1907-002               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |        9 | 2019-07-31 | VMK-1907-005               |            | 2019-07-31 | 2019-07-31 | IDR      |        48208712.04 |    1 |
|    0 |       10 | 2019-07-31 | VMK-1907-004               |            | 2019-07-31 | 2019-07-31 | IDR      |       916482350.34 |    1 |
|    0 |       11 | 2019-07-31 | VMK-1907-001               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |       12 | 2019-07-31 | VMK-1907-003               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |       13 | 2019-07-31 | VMK-1907-001               |            | 2019-07-31 | 2019-07-31 | IDR      |       419723211.71 |    1 |
|    0 |       14 | 2019-07-31 | VMK-1907-003               |            | 2019-07-31 | 2019-07-31 | IDR      |        18261030.49 |    1 |
|    0 |       15 | 2019-07-31 | VMK-1907-002               |            | 2019-07-31 | 2019-07-31 | IDR      |            4093300 |    1 |
|    0 |       16 | 2019-07-31 | VMK-1907-006               |            | 2019-07-31 | 2019-07-31 | IDR      | 504065292.70000005 |    1 |
|    0 |       17 | 2019-07-31 | VMK-1907-001- jurnal balik |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |       18 | 2019-07-31 | VMK-1907-007               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |       19 | 2019-07-31 | VMK-1907-001- jurnal balik |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |       20 | 2019-07-31 | VMK-1907-001- jurnal balik |            | 2019-07-31 | 2019-07-31 | IDR      |            4365000 |    1 |
|    0 |       21 | 2019-07-31 | VMK-1907-008               |            | 2019-07-31 | 2019-07-31 | IDR      |          418928017 |    1 |
|    0 |       22 | 2019-07-31 | VMK-1907-009               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |       23 | 2019-07-31 | VMK-1907-009               |            | 2019-07-31 | 2019-07-31 | IDR      |       430771400.17 |    1 |
|    0 |       24 | 2019-07-31 | VMK-1907-010               |            | 2019-07-31 | 2019-07-31 | IDR      |         1355000.62 |    1 |
|    0 |       25 | 2019-07-31 | VMK-1907-011               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |       26 | 2019-07-31 | VMK-1907-011               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |       27 | 2019-07-31 | VMK-1907-011               |            | 2019-07-31 | 2019-07-31 | IDR      |                  0 |    1 |
|    0 |       28 | 2019-07-31 | VMK-1907-007               |            | 2019-07-31 | 2019-07-31 | IDR      |            4972000 |    1 |
|    0 |       29 | 2019-07-31 | VMK-1907-011               |            | 2019-07-31 | 2019-07-31 | IDR      |      2732964862.66 |    1 |
|    0 |       30 | 2019-08-30 | VMK-1908-001               |            | 2019-08-30 | 2019-08-30 | IDR      |         4277607.91 |    1 |
|    0 |       31 | 2019-08-31 | VMK-1908-002               |            | 2019-08-31 | 2019-08-31 | IDR      |            1488862 |    1 |
|    0 |       32 | 2019-08-31 | VMK-1908-003               |            | 2019-08-31 | 2019-08-31 | IDR      |            3512228 |    1 |
|    0 |       33 | 2019-08-31 | VMK-1908-004               |            | 2019-08-31 | 2019-08-31 | IDR      |            4787799 |    1 |
|   35 |        1 | 2019-08-07 | VMK-1908-001               |            | 2019-08-07 | 2019-08-07 | IDR      |             180000 |    1 |
|   35 |        2 | 2019-08-07 | VMK-1908-002               |            | 2019-08-07 | 2019-08-07 | IDR      |             180000 |    1 |
+------+----------+------------+----------------------------+------------+------------+------------+----------+--------------------+------+
35 rows in set (0.001 sec)

ichtus's Website

  • From: Indonesia, Surabaya
  • Registered: 01/08/2012
  • Posts: 36

Re: error message : Incorrect date value: '' for column

@Braath Waate
thanks, that is already done by:

+++ b/core/includes/db/connect_db_mysqli.inc
@@ -10,7 +10,7 @@
     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
 ***********************************************************************/
 define('DB_DUPLICATE_ERROR', 1062);
-define('SQL_MODE', 'STRICT_ALL_TABLES'); // prevents SQL injection with silent field content truncation
+define('SQL_MODE', ''); // well tested

thanks

ichtus's Website