March 31, 2020 at 7:28 pm
Is there a way to find the field this error is being generated by? I am trying to bring data from an AS400 to SQL. I am attempting to convert 3 fields to date formats. I have a staging table that converts them to nvarchar(10) then when inserting into the final table I do IDATE(field) = 1 then I convert to date else I insert null. if I comment out the date fields it still throws that error. I have increased the size of all the fields, most were decimal, char and numeric. I will post the code of original table and staging table.
March 31, 2020 at 7:30 pm
Original Table from AS400
SELECT
NOUSGE, /* NOUSGE CHARACTER(1) DO NOT USE FLAG */
MVEND, /* MVEND CHARACTER(7) Vendor Number */
SPACE2, /* SPACE2 CHARACTER(2) * */
MNAME, /* MNAME CHARACTER(30) Vendor Name */
MADDR1, /* MADDR1 CHARACTER(30) Acct Addr Line 1 */
MADDR2, /* MADDR2 CHARACTER(30) Acct Addr Line 2 */
MCITY, /* MCITY CHARACTER(15) Acct City */
MSTATE, /* MSTATE CHARACTER(2) Acct State */
MZIP, /* MZIP CHARACTER(9) Acct Zip */
MAGENT, /* MAGENT CHARACTER(2) Purchsng Agent # */
M1099C, /* M1099C CHARACTER(1) 1099 Code */
LCVCDE, /* LCVCDE CHARACTER(1) L/C Vendor Code Y/N */
MPHONE, /* MPHONE NUMERIC(10,0) Acct Phone # */
MCNTCT, /* MCNTCT CHARACTER(20) Acct Contact Person */
MSHORT, /* MSHORT CHARACTER(10) Short Name */
OS#VN1, /* OS#VN1 NUMERIC(3,0) OffSite Asmb Shipto# */
MTMCOD, /* MTMCOD CHARACTER(2) Terms Code */
M1099I, /* M1099I CHARACTER(15) 1099 ID # */
MFAX, /* MFAX NUMERIC(10,0) Acct Fax # */
MDIRCT, /* MDIRCT CHARACTER(1) Ship Direct */
MN$VND, /* MN$VND NUMERIC(9,2) Min $ Order Amt */
CP$VND, /* CP$VND NUMERIC(9,2) Coop. $ Agreement */
MFRGHT, /* MFRGHT CHARACTER(1) Freight */
MP$YTD, /* MP$YTD DECIMAL(11,2) Purchases YTD */
MDTYTD, /* MDTYTD DECIMAL(9,2) Discnt Taken YTD */
MDLYTD, /* MDLYTD DECIMAL(9,2) Discnt Lost YTD */
MP$YLR, /* MP$YLR DECIMAL(11,2) Purchases Last Year */
MDTLYR, /* MDTLYR DECIMAL(9,2) Disc Taken Last Year */
MDLLYR, /* MDLLYR DECIMAL(9,2) Disc Lost Last Year */
MDELET, /* MDELET CHARACTER(1) Dlt Cd */
SPACB1, /* SPACB1 CHARACTER(1) * */
ATCVND, /* ATCVND CHARACTER(1) Buyer Attn Cd */
SPACC1, /* SPACC1 CHARACTER(1) * */
MLACTD, /* MLACTD NUMERIC(8,0) Last Actvty Date */
MLPMTD, /* MLPMTD NUMERIC(8,0) Last Pymnt Date */
CTLMTD, /* CTLMTD NUMERIC(10,0) CSR Phone # */
CFXMTD, /* CFXMTD NUMERIC(10,0) CSR FAx # */
CNTMTD, /* CNTMTD CHARACTER(20) CSR Contact */
LCPMTD, /* LCPMTD NUMERIC(5,0) L/C Cost% */
OSAVND, /* OSAVND CHARACTER(1) Offsite Asmb Vendor */
CARVND, /* CARVND CHARACTER(13) Freight Carrier */
CM1VND, /* CM1VND CHARACTER(30) PO Default Comment1 */
CM2VND, /* CM2VND CHARACTER(30) PO Default Comment2 */
FRTVND, /* FRTVND CHARACTER(23) Vendor Freight Terms */
DPTMND, /* DPTMND NUMERIC(3,0) PO Default Dept# */
OS#VN2, /* OS#VN2 NUMERIC(4,0) OffSite Asmb ShipTo# */
LDTVND, /* LDTVND NUMERIC(3,0) Vendor Lead- Time Days */
MNOVND, /* MNOVND NUMERIC(7,0) Minimum Order Qty */
MNUVND, /* MNUVND CHARACTER(2) Min Ordr U of M */
EMLVND, /* EMLVND CHARACTER(60) A/P Email Address */
ADTVND, /* ADTVND NUMERIC(8,0) Vendor Add Date */
CMTVND, /* CMTVND CHARACTER(60) Vendor Comment */
CMLVND, /* CMLVND CHARACTER(40) CSR Email Address */
RTLVND, /* RTLVND NUMERIC(10,0) SalesRep Phone # */
RFXVND, /* RFXVND NUMERIC(10,0) SalesRep Fax # */
RNTVND, /* RNTVND CHARACTER(20) SalesRep Contact */
RMLVND, /* RMLVND CHARACTER(40) SalesRep Email Addrs */
MA1VND, /* MA1VND CHARACTER(30) Mailing Address #1 */
MA2VND, /* MA2VND CHARACTER(30) Mailing Address #2 */
MCYVND, /* MCYVND CHARACTER(15) Mailing City */
MSTVND, /* MSTVND CHARACTER(2) Mail State */
MZPVND, /* MZPVND CHARACTER(9) Mail Zip Code */
VDSVND, /* VDSVND NUMERIC(4,4) VENDOR DSCNT PCNT */
BX#VND, /* BX#VND NUMERIC(1,0) 1099 BOX# */
BIPVND, /* BIPVND CHARACTER(1) BUYER INITIATED PAYMNT */
SPA251 /* SPA251 CHARACTER(251) * */
FROM QS36F.TABLE99;
To TABLE IN SQL
USE [QS36f]
GO
/****** Object: Table [dbo].[A99MAST] Script Date: 3/31/2020 2:40:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[A99MAST](
[DO NOT USE FLAG] [nvarchar](2) NULL,
[VENDOR NUMBER] [nvarchar](12) NULL,
[SPACE2] [nvarchar](12) NULL,
[VENDOR NAME] [nvarchar](50) NULL,
[ACCT ADDRESS LINE 1] [nvarchar](50) NULL,
[ACCT ADDRESS LINE 2] [nvarchar](50) NULL,
[ACCOUNT CITY] [nvarchar](25) NULL,
[ACCOUNT STATE] [nvarchar](4) NULL,
[ACCOUNT ZIP] [nvarchar](10) NULL,
[PURCHASING AGENT] [nvarchar](4) NULL,
[1099 CODE] [nvarchar](10) NULL,
[L/C VENDOR CODE Y/N] [nvarchar](2) NULL,
[ACCOUNT PHONE] [nvarchar](15) NULL,
[ACCOUNT CONTACT PERSON] [nvarchar](50) NULL,
[SHORT NAME] [nvarchar](50) NULL,
[OFFSITE ASSEM SHIPTO1] [nvarchar](6) NULL,
[TERMS CODE] [nvarchar](6) NULL,
[1099 ID] [nvarchar](15) NULL,
[ACCOUNT FAX] [nvarchar](15) NULL,
[SHIP DIRECT] [nvarchar](4) NULL,
[MIN ORDER AMOUNT] [nvarchar](16) NULL,
[COOP. AGREEMENT] [nvarchar](16) NULL,
[FREIGHT] [nvarchar](4) NULL,
[PURCHASES YTD] [nvarchar](16) NULL,
[DISCOUNT TAKEN YTD] [nvarchar](16) NULL,
[DISCOUNT LOST YTD] [nvarchar](16) NULL,
[PURCHASES LAST YR] [nvarchar](16) NULL,
[DISCOUNT TAKEN LAST YR] [nvarchar](16) NULL,
[DISCOUNT LOST LAST YR] [nvarchar](16) NULL,
[DLT CD] [nvarchar](4) NULL,
[SPACB1] [nvarchar](15) NULL,
[BUYER ATTN CD] [nvarchar](15) NULL,
[SPACC1] [nvarchar](25) NULL,
[LAST ACTIVITY DATE] [nvarchar](16) NULL,
[LAST PAYMENT DATE] [nvarchar](16) NULL,
[CSR PHONE] [nvarchar](15) NULL,
[CSR FAX] [nvarchar](15) NULL,
[CSR CONTACT] [nvarchar](50) NULL,
[L/C COST PERCENT] [nvarchar](15) NULL,
[OFFSITE ASMB VENDOR] [nvarchar](6) NULL,
[FREIGHT CARRIER] [nvarchar](50) NULL,
[PO DEFAULT COMMENTS1] [nvarchar](255) NULL,
[PO DEFAULT COMMENTS2] [nvarchar](255) NULL,
[VENDOR FREIGHT TERMS] [nvarchar](30) NULL,
[PO DEFAULT DEPT] [nvarchar](15) NULL,
[OFFSITE ASMB SHIPTO] [nvarchar](15) NULL,
[VENDOR LEAD-TIME DAYS] [nvarchar](6) NULL,
[MINIMUM ORDER QTY] [nvarchar](6) NULL,
[MIN ORDER UOM] [nvarchar](6) NULL,
[A/P EMAIL ADDRESS] [nvarchar](75) NULL,
[VENDOR ADD DATE] [nvarchar](15) NULL,
[VENDOR COMMENTS] [nvarchar](255) NULL,
[CSR EMAIL ADDRESS] [nvarchar](40) NULL,
[SALESREP PHONE] [nvarchar](15) NULL,
[SALESREP FAX] [nvarchar](15) NULL,
[SALESREP CONTACT] [nvarchar](50) NULL,
[SALESREP EMAIL ADDRESS] [nvarchar](50) NULL,
[MAILING ADDRESS1] [nvarchar](50) NULL,
[MAILING ADDRESS2] [nvarchar](50) NULL,
[MAILING CITY] [nvarchar](50) NULL,
[MAILING STATE] [nvarchar](25) NULL,
[MAILING ZIP] [nvarchar](9) NULL,
[VENDOR DSCNT PCNT] [nvarchar](15) NULL,
[1099 BOX] [nvarchar](6) NULL,
[BUYER INITIATED PAYMNT] [nvarchar](255) NULL,
[SPA251] [nvarchar](255) NULL
) ON [PRIMARY]
GO
March 31, 2020 at 7:38 pm
Is your data really double-byte (from double-byte languages like Arabic and Japanese?) If not, just use VARCHAR instead.
March 31, 2020 at 7:40 pm
No it isn't and youre talking about the decimal fields correct?
March 31, 2020 at 8:07 pm
NVARCHAR() fields are text.
March 31, 2020 at 8:15 pm
Yes they are, no calculations will be done on them.
March 31, 2020 at 10:27 pm
few comments on this
Date fields - they look like numbers representing a date on format DDMMYYYY but you need to speak with the owner of the software on the AS400 to confirm this and to see if they have values representing particular dates which you may need to reproduce.
to convert these dates you should do something like - right('0' + convert(varchar(8), datefield), 8) to get a string representation of the date and then use substrings on it to convert to format yyyymmdd for easy conversion to a date
DO NOT use isdate - it will return true even for bad representations of a date - e.g. isdate('2007' will yield a valid date)
April 8, 2020 at 10:47 am
To find out which values are causing the problem, you could use TRY_CAST(YourColumn AS DATE). The values that can't be cast will be null in the new table. Then you can look at the same rows in the originating table to see what was in those columns. Note: this will still allow a few invalid dates through (as mentioned by Frederico regarding ISDATE above).
April 8, 2020 at 4:56 pm
I think you also find that the Date data coming from an AS400 or IBM PowerSystem will contain all 0's for "null" dates.
Also, don't use VARCHAR(10) to hold string representations of dates. You're just chewing up an additional 2 bytes (20%) per entry. Use CHAR(10) instead and, like the other said, don't use NVARCHAR(10) for such things.
And, seriously... using NVARCHAR(9) for a Zip Code column is just a crazy waste of byte space.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply