[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 842
  • Last Modified:

Conversion failed when converting date and/or time from character string.

This is my first official question on experts exchange, so please bear with me.  Have a "string" CSV field TRNSDATE that I am importing into SQL DB field that is "datetime" via SIS.  The below  works for a sister company (all database properties, csv properties, etc. appear identical) but give me the above "conversion failed...." error for this one company.  I'm stumped and appreciate suggestions.

insert into partsros([invnum/ponum], partnum, partnumdesc, [tran], tc, trnsdate, qty, ucost, extcost, extsale, counterman, custnum)
select partsrostemp.[invnum/ponum],
partsrostemp.partnum,
partsrostemp.partnumdesc,
partsrostemp.[tran],
partsrostemp.tc,
convert(datetime,partsrostemp.trnsdate),
convert(money,partsrostemp.qty),
convert(money,partsrostemp.ucost),
convert(money,partsrostemp.extcost),
convert(money,partsrostemp.extsale),
partsrostemp.counterman,
partsrostemp.custnum
from partsrostemp
left outer join partsros on partsrostemp.[invnum/ponum] = partsros.[invnum/ponum]
where partsros.[invnum/ponum] is null

Open in new window

0
JAYDUB
Asked:
JAYDUB
  • 4
  • 3
  • 2
2 Solutions
 
TMarkham1Commented:
Check all values in the partsrostemp table for the trnsdate... make sure you have no empty string values, or values that cannot be converted to a datetime from a string. I'll try to come up with a test for that table and post in a moment.
0
 
TMarkham1Commented:
Actually, if you have any null values in the trnsdate field of the partsrostemp table , they will be inserted as NULL in the partsros table. If there are empty string values, they will be inserted as 1/1/1900.

You can use the T-SQL "ISDATE" function to filter out bad dates (or act appropriately when one is found).
0
 
TMarkham1Commented:
So, depending upon what you want to do when trnsdate contains a bad date you can either place the ISDATE in the WHERE clause to exclude the rows from being inserted, or if you want all rows inserted but convert trnsdate to null for bad dates you could use a CASE statement in the SELECT. See below for examples of both:
--Exclude inserting rows with bad dates
insert into partsros([invnum/ponum], partnum, partnumdesc, [tran], tc, trnsdate, qty, ucost, extcost, extsale, counterman, custnum)
select partsrostemp.[invnum/ponum],
partsrostemp.partnum,
partsrostemp.partnumdesc,
partsrostemp.[tran],
partsrostemp.tc,
convert(datetime,partsrostemp.trnsdate),
convert(money,partsrostemp.qty),
convert(money,partsrostemp.ucost),
convert(money,partsrostemp.extcost),
convert(money,partsrostemp.extsale),
partsrostemp.counterman,
partsrostemp.custnum
from partsrostemp
left outer join partsros on partsrostemp.[invnum/ponum] = partsros.[invnum/ponum]
where partsros.[invnum/ponum] is null
AND ISDATE(partsrostemp.trnsdate) = 1


--Insert all records, but NULL values for trnsdate when an invalid value is found
insert into partsros([invnum/ponum], partnum, partnumdesc, [tran], tc, trnsdate, qty, ucost, extcost, extsale, counterman, custnum)
select partsrostemp.[invnum/ponum],
partsrostemp.partnum,
partsrostemp.partnumdesc,
partsrostemp.[tran],
partsrostemp.tc,
CASE WHEN ISDATE(partsrostemp.trnsdate) = 1 THEN convert(datetime,partsrostemp.trnsdate) ELSE NULL END,
convert(money,partsrostemp.qty),
convert(money,partsrostemp.ucost),
convert(money,partsrostemp.extcost),
convert(money,partsrostemp.extsale),
partsrostemp.counterman,
partsrostemp.custnum
from partsrostemp
left outer join partsros on partsrostemp.[invnum/ponum] = partsros.[invnum/ponum]
where partsros.[invnum/ponum] is null 

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Reza RadCommented:
could you attach your csv file, and sql server table structure here?
0
 
JAYDUBAuthor Commented:
Below is my table structure for partsrostemp and I'll attach the CSV.  My problem appears to be in the SSIS since my partsrostemp table is empty.  I discovered this when attempting to clean the trnsdate as suggested.  I did make the changes to the script using "isdate" and all went great!  but no rows impacted since nothing in partsrostemp table.  I appreciate all the help....

USE [7709528881]
GO

/****** Object:  Table [dbo].[partsrostemp]    Script Date: 12/22/2009 07:23:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[partsrostemp](
	[INVNUM/PONUM] [varchar](255) NULL,
	[PARTNUM] [varchar](255) NULL,
	[PARTNUMDESC] [varchar](255) NULL,
	[TRAN] [varchar](255) NULL,
	[TC] [varchar](255) NULL,
	[TRNSDATE] [varchar](max) NULL,
	[QTY] [varchar](max) NULL,
	[UCOST] [varchar](max) NULL,
	[EXTCOST] [varchar](max) NULL,
	[EXTSALE] [varchar](max) NULL,
	[COUNTERMAN] [varchar](255) NULL,
	[CUSTNUM] [varchar](255) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Open in new window

PARTSROS.csv
0
 
Reza RadCommented:
problem is that you have three row in your csv that has inappropriate columns order
I marked them in the image below

1.JPG
0
 
Reza RadCommented:
OK
this seems that you didn't set TEXT QUALIFIER
your column values has a comma ( , ) sometimes in a value between two ( " ) , but csv file will not consider them as a value,  this will separate by comma. If you want to have comma in your value you must set TEXT QUALIFIER as (   "    )
this will consider all things between two " as a value
0
 
Reza RadCommented:
I attached image for help

2.JPG
0
 
JAYDUBAuthor Commented:
Thanks a million. This worked resulting in me looking harder at the SSIS and I noticed a bad connection to the partsrostemp file.  Once fixed, it ran like a charm.  I also added "isnumeric" to the money fields much like you had me do with the "isdate" for the date fields...this too worked like a charm.  Thanks guys!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now