Finding Zero Dates on Customer DataBases

Owned by Diane Partridge
Last updated: May 08, 2022
2 min read

Overview

We can get cases where:

  1. Customer list views show as blank when you open a module

  2. Run processes in readysell and you get a error message can be on a report.

The solution is to use the find in database option in Heidi sql to identify which data file has the zero/null date/time.
An database related error has occurred. Please report this issue to Readysell support.

In the example below, you would firstly look for the ‘From’ to determine the file that has the issue.


In the example below the datafile is the Card File.

You would then open up Heidi and follow the steps below:

  1. Find and highlight the Card File

  2. Right click on the file

  3. Select ‘Find text on Server’

     

  4. In the field ‘text to find’ type in 0000- and click on Find

     

     

  5. Check the Number of found rows if it exceeds 0 then you have a issue

     

  6. Click on See Results and scroll through to all the fields usually date type fields that have 0000-0000-0000 and update them

     

  7. Arrow down and back up to update the file

     

'DevExpress.Xpo.DB.Exceptions.SqlExecutionErrorException: Executing Sql 'select N0.`Oid`,N0.`CreatedAt`,N0.`CreatedBy`,N0.`LastModifiedAt`,N0.`LastModifiedBy`,N0.`WorkstationOid`,N0.`ClientRecordOid`,N0.`OfficeBrandsDataLastUpdated`,N0.`PosDataLastUpdated`,N0.`CardID`,N0.`Name`,N0.`DisplayName`,N0.`Image`,N0.`ReferenceNumber`,N0.`WebCustomerName`,N0.`OfficeBrandsNationalAccountID`,N0.`SupplierStockServiceBranchCode`,N0.`SalesforceID`,N0.`CardType`,N0.`CardStatus`,N0.`DateCreated`,N0.`DateClosed`,N0.`LastPurchaseInvoiceDate`,N0.`LastSaleInvoiceDate`,N0.`LastCustomerPaymentDate`,N0.`Abn`,N0.`Status`,N0.`ChargeTo`,N0.`Parent`,N0.`SendDocumentsToParent`,N0.`DiscountFromList`,N0.`MinimumOrderValue`,N0.`MinimumOrderQuantity`,N0.`CustomerPaymentTerm`,N0.`EarlyPaymentDiscountTerms`,N0.`EarlyPaymentDiscountPercent`,N0.`CustomerTender`,N0.`CustomerTenderMode`,N0.`PaymentTender`,N0.`SupplierPaymentTerm`,N0.`SupplierTender`,N0.`CustomerBackOrderAllowed`,N0.`SupplierBackOrderAllowed`,N0.`SplitMeterInvoicesByMachine`,N0.`OverallRankIndex`,N0.`OverallRankValue`,N0.`QuantityRankIndex`,N0.`QuantityRankValue`,N0.`MarginRankIndex`,N0.`MarginRankValue`,N0.`ValueRankIndex`,N0.`ValueRankValue`,N0.`AverageMonthlyInvoiceCount`,N0.`AverageMonthlyInvoiceValue`,N0.`AverageMonthlyInvoiceLineValue`,N0.`MaximumShipmentsPerSale`,N0.`TaxCode`,N0.`WebsiteUrl`,N0.`AvailableOnPointOfSale`,N0.`ShowInSalesReporting`,N0.`LastContactDate`,N0.`NextContactDate`,N0.`NumberOfStaff`,N0.`SalesBudget`,N0.`Industry`,N0.`ProspectSource`,N0.`LandingPageUrl`,N0.`ProspectStatus`,N0.`ProspectDisqualificationReason`,N0.`ProspectRating`,N0.`SalesGroup`,N0.`IsProspect`,N0.`IsCustomer`,N0.`CurrentCustomerBalanceTotal`,N0.`CurrentCustomerBalanceCurrent`,N0.`CurrentCustomerBalance30Days`,N0.`CurrentCustomerBalance60Days`,N0.`CurrentCustomerBalance90Days`,N0.`CurrentCustomerBalance120Days`,N0.`CurrentCustomerBalanceForward`,N0.`CurrentCustomerBalanceOverdue`,N0.`CustomerCreditLimit`,N0.`CustomerCreditLimitExceeded`,N0.`AddExtraChargeProduct`,N0.`StatementDeliveryMethod`,N0.`InvoiceMode`,N0.`Carrier`,N0.`CostCentre`,N0.`DeliveryDocketReport`,N0.`InvoiceReport`,N0.`StatementReport`,N0.`PriceLevel`,N0.`CustomerOrderValidation`,N0.`DefaultCustomerOrder`,N0.`SupplierOrderRequired`,N0.`SupplierIgnoresMinimumOrderQuantity`,N0.`OrdersOnWeb`,N0.`ServicesPerson`,N0.`TradingAs`,N0.`CustomerStartDate`,N0.`CustomerEndDate`,N0.`CustomerContractStartDate`,N0.`CustomerContractEndDate`,N0.`IsSupplier`,N0.`CurrentSupplierBalanceTotal`,N0.`CurrentSupplierBalanceCurrent`,N0.`CurrentSupplierBalance30Days`,N0.`CurrentSupplierBalance60Days`,N0.`CurrentSupplierBalance90Days`,N0.`CurrentSupplierBalance120Days`,N0.`CurrentSupplierBalanceForward`,N0.`CurrentSupplierBalanceOverdue`,N0.`SupplierCreditLimit`,N0.`SupplierCreditLimitExceeded`,N0.`Account`,N0.`Currency`,N0.`ExWorksLeadTime`,N0.`LeadTime`,N0.`CustomerLeadTime`,N0.`OperatingDays`,N0.`ReorderCycle`,N0.`AutoEmailRemittances`,N0.`SupplierAccountNumber`,N0.`BpayBillerCode`,N0.`FmAuditAccountID`,N0.`IsManufacturer`,N0.`DeliveryDocketDeliveryMethod`,N0.`InvoiceDeliveryMethod`,N0.`LoyaltyPointsGained`,N0.`LoyaltyPointsRedeemed`,N0.`LoyaltyPointsExpired`,N0.`LoyaltyPointsBalance`,N0.`SaleTerritory`,N0.`PrimarySalesPerson`,N0.`CustomerSupplyType`,N0.`CanHire`,N0.`WebOrderApproval`,N0.`MinimumForWebOrderApproval`,N0.`LoyaltyPointsMode`,N0.`LoyaltyPointsValue`,N0.`Site`,N0.`Lapsed`,N0.`EdiEndpointUrl`,N0.`EdiUsername`,N0.`EdiPassword`,N0.`CreatedWorkstation`,N0.`CustomerPaymentReference` from `Card` N0' with parameters '' exception 'MySql.Data.Types.MySqlConversionException: Unable to convert MySQL date/time value to System.DateTime

Alternatively, if a file is found to have a 0000-00 date, then you can just run this script
eg the table of Card , the datecreated has been found to be 0000-0000-00
Update card c set c.datecreated = now() where c.datecreated < "19000101";