Web Connection
wwSQL.InsertDataFromCursor and Dates
Gravatar is a globally recognized avatar based on your email address. wwSQL.InsertDataFromCursor and Dates
  Michael Hogan (Ideate Hosting)
  All
  Nov 16, 2021 @ 02:56pm

I'm getting the following error from wwSQL.InsertDataFromCursor:

Conversion failed when converting date and/or time from character string. [1526:241]

Is this because of blank dates in my cursor? Do I need to replace all empty dates to the magic SQL 'zero' date, or otherwise massage the source cursor? Perhaps the length of the INSERT ???

The typical error in its entirety is:

Conversion failed when converting date and/or time from character string. [1526:241]
INSERT DBPACSSH ([ACTUALFRT],[APPLYFRT],[APPLYTO],[APPROVE13],[ARDUEDATE],[ASHIPPED],[BADDRESS2],[BANK800],[BANKNAME],[BCITY],[BCOMPANY],[BCONTACT],[BCOUNTRY],[BEMAIL],[BFAX],[BILLTONO],[BOLPRINTED],[BOVERRIDE],[BPHONE],[BPORTABLE],[BREGION],[BSTATE],[BUPSTYPE],[BUYEREXT],[BUYERNO],[BUYERPHONE],[BZIP],[CAPPROV],[CASHTEND],[CATID],[CCADDR],[CCAMOUNT],[CCAPRDATE],[CCNAME],[CCNO],[CCNOTE],[CCOPTION],[CCTYPE],[CCZIP],[CERTNO],[CHARGEBACK],[CHECKNO],[CHECKTOTAL],[CHGBY],[CHGDATE],[CODCHRG],[CODTOT],[COMMTOT],[COST],[COUPON],[CPREAPPROV],[CREDITONLY],[CREDTOT],[CURLINENO],[CUSTNO],[CVV],[DEALERREF],[DECLRD_VAL],[DELFRT],[DELWT],[DISCOUNT],[DISCOUNTUSED],[DISCPCT],[DISTMAN],[DNS],[DOCK],[DROPNEEDOR],[EDIACKDATE],[ENTBY],[ENTDATE],[EOP],[EOPDUEDATE],[FEDEXCOST],[FEDEXMENU],[FEDEXSAT],[FEDEXTYPE],[FIRSTACK],[FIRSTORDER],[FPOVERRIDE],[FPP],[FRAUD],[FRTACCTNO],[FRTALLAMT],[FRTALLPCT],[FRTCOLLECT],[FTCDATE],[GROUP],[GROUPEOP],[HANDLING],[HANDOVERRIDE],[INOTE1],[INOTE2],[INOTE3],[INOTE4],[INSURE],[INUSE],[INVDATE],[INVOICENOW],[INVPRINT],[IP],[ITMTOT],[L8ST_SHPD8],[LASTPKGID],[LOWPRICEOK],[MAXOK],[MISCCHRG],[MISCWHY],[MIXEDFRT],[MMADDR],[MMCVV],[MMOK],[MMZIP],[MULTIPAGE],[NOINVOICE],[OADDRESS1],[OADDRESS2],[OCITY],[OCOMPANY],[OCOUNTRY],[OEMAIL],[OFAX],[ONOTE],[OOVERRIDE],[OPENTOT],[OPHONE],[OPORTABLE],[ORDDATE],[ORDERPROG],[ORDERWT],[ORDNO],[ORDNO_REF],[ORDSTAT],[ORDTOT],[ORDTYPE],[OREGION],[OSOURCE],[OSTATE],[OUPSTYPE],[OVERFRTPOL],[OVERRIDECC],[OVERRIDEMS],[OZIP],[PARTIALOK],[PAYTYPE],[PICKBATCH],[PICKNOW],[PKGCOUNT],[PMFCODE],[PNOTE],[PONO],[POSTDATE],[PREPAID],[PREPAIDUSED],[PRINTMSDS],[PRONO],[REPLACE4ORD],[RPNO],[RSHIPDATE],[RWCODE],[RWUSED],[SADDRESS1],[SADDRESS2],[SALESTAX],[SCAC],[SCITY],[SCOMPANY],[SCONTACT],[SCOUNTRY],[SEMAIL],[SFAX],[SHIPAIR],[SHIPBLIND],[SHIPCHRG],[SHIPCOUNT],[SHIPOVERRIDE],[SHIPTIME],[SHIPTONO],[SHIPTYPE],[SHIPTYPEBO],[SHOPPERID],[SIGNATURE],[SNOTE],[SOVERRIDE],[SPECIAL],[SPHONE],[SPORTABLE],[SREGION],[SSTATE],[SUPSTYPE],[SUSPEND1],[SUSPEND10],[SUSPEND11],[SUSPEND12],[SUSPEND13],[SUSPEND2],[SUSPEND3],[SUSPEND4],[SUSPEND5],[SUSPEND6],[SUSPEND7],[SUSPEND8],[SUSPEND9],[SZIP],[TAXABLE],[TAXRATE],[TERMSDESC],[TOTALWT],[TRAILERNO],[TRUCKAMT],[TRUCKVEND],[USEFEDEX],[USEFRTACCT],[WAREHOUSE],[WEBDISC],[WEBHOLD],[WNOTE]) VALUES (0,0,0,0,0,0,'','','','TAYLOR','ADAM GURR','ADAM GURR','','','',1,0,1,'415-851-91368','','','AZ','C','',2,'','85939','',0,'INT263','',0,0,'','','',0,'','',0,0,'AMAZON',0,'maya','maya',0,0,0,82.71,'','',0,0,0,438292,'','',71.60,0,0,0,0,0,0,'','',0,0,'aaron','aaron',0,0,0,0,0,'',0,0,0,0,0,'',0,0,0,0,'',0,0,0,'','','','',0,'EC2AMAZ-VBGE34T','EC2AMAZ-VBGE34T',0,0,'',159.11,159.11,0,0,0,0,'',0,0,0,0,0,0,0,'8111 CONCORD MILLS BLVD STE 8461','','HAMMOND','ERIN MCCARTHY','','technicalpreicisonproducts@gmail.com','','',1,159.11,'602-671-6610','','','',0,10555020,0,'SHIPPED',159.11,'','','1','IN','',0,0,0,'28027-6462',0,'A',0,0,0,'A','Amazon#: 112-3164343-7011455
The line item(s) for this order have been automatically flagged for drop ship. Review all line item(s) in this order.
Qty: 1 | Price: 159.11 | Item: SCKP450-BK - Replacement For Skinz Pro-series Console Knee Pads - Ski-doo 850 2017
maya       11/15/2021 10:57:41 AM: ds - CC 100602871','112-3164343-7011455','112-3164343-7011455',159.11,0,0,'','',5741,5741,'',0,'3151 WEST PAPERMILL','',0,'','TAYLOR','ADAM GURR','ADAM GURR','','','',0,0,0,1,1,0,1,'M3','M3',0,0,'',1,0,'415-851-91368','','','AZ','C',0,0,0,0,0,0,0,0,0,0,0,0,0,'85939',159.11,0,'',0,'',0,0,0,0,100,0,0,'')
Gravatar is a globally recognized avatar based on your email address. re: wwSQL.InsertDataFromCursor and Dates
  Rick Strahl
  Michael Hogan (Ideate Hosting)
  Nov 16, 2021 @ 10:47pm

Try it?

But yeah, empty dates are problem in SQL as SQL Server doesn't have an empty date (other than NULL). Doing it any other way would result in a invalid data - so you have to choose that data up front.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: wwSQL.InsertDataFromCursor and Dates
  Michael Hogan (Ideate Hosting)
  Rick Strahl
  Nov 17, 2021 @ 05:08am

Ahhh.... OK. So I have to replace empty dates with null and make sure to allow nulls on the SQL side. I'll give that a shot.

Thanks.

Gravatar is a globally recognized avatar based on your email address. re: wwSQL.InsertDataFromCursor and Dates
  Michael Hogan (Ideate Hosting)
  Michael Hogan (Ideate Hosting)
  Nov 17, 2021 @ 05:52am

It sure would be nice to have wwSQL detect and correct empty date fields during export...

Gravatar is a globally recognized avatar based on your email address. re: wwSQL.InsertDataFromCursor and Dates
  Michael Hogan (Ideate Hosting)
  Rick Strahl
  Nov 17, 2021 @ 11:29am

That didn't fix it. The error message suggests that wwSQL is sending a zero instead of a null for dates:

Conversion failed when converting date and/or time from character string. [1526:241]

INSERT DBPACSSH ([ACTUALFRT],[APPLYFRT],[APPLYTO],[APPROVE13],[ARDUEDATE],[ASHIPPED],[BADDRESS2]

VALUES (0,0,0,0,0,0,'','','','WESTWOOD','SAM JOHNSON'

I'm using your wwsql.vcx class - I'm having trouble identifying the version. I'll try grabbing it from the latest webconnect installation.

Gravatar is a globally recognized avatar based on your email address. re: wwSQL.InsertDataFromCursor and Dates
  Michael Hogan (Ideate Hosting)
  Michael Hogan (Ideate Hosting)
  Nov 17, 2021 @ 12:08pm

Ahhh... I'm seeing it in wwutils... Checking further.

Gravatar is a globally recognized avatar based on your email address. re: wwSQL.InsertDataFromCursor and Dates
  Michael Hogan (Ideate Hosting)
  Michael Hogan (Ideate Hosting)
  Nov 17, 2021 @ 12:29pm

Yup - my fault (per usual). Didn't 'DO wwUTILS' so my code wasn't finding TimeToCStrict()

© 1996-2021