Finding Zero Dates on Customer DataBases
Overview
We can get cases where:
Customer list views show as blank when you open a module
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:
Find and highlight the Card File
Right click on the file
Select ‘Find text on Server’
In the field ‘text to find’ type in 0000- and click on Find
Check the Number of found rows if it exceeds 0 then you have a issue
Click on See Results and scroll through to all the fields usually date type fields that have 0000-0000-0000 and update them
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";