This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(310.4, 79%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBP%3C/text%3E%3C/svg%3E)
We have an Excel Data Colum CarrierInvoicedDate which has the format MM/DD/YYYY and exists in the Excel spreadsheet with a General format.
Our data pump to a SQL Server Table where we have CarrierInvoicedDate defined as a DATE Data Type is choking and resulting in...
"Invalid character value for cast specification"
Do we need to reformat this CarrierInvoicedDate Data Column in Excel prior to trying to pump the data to our SQL Server Table? And reformat it in Excel to what? Date?
Thanks in advance for your review and am hopeful for a reply.
Hi @Bobby P ,
Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
Hi @Bobby P ,
I would suggest to use Derived Column Transformation to convert MM/DD/YYYY into the yyyy-MM-dd format. After that SQL Server column with the DATE format will accept it without any problem.
Hi @Bobby P ,
In addition of using Derived Column Transformation, you may also go to advance editor for excel source-->input and output properties-->output column.
Change the datatype to [DT-Date] or [DT_DBDATE].
Regards，
Zoe Hui
If the answer is helpful, please click "Accept Answer" and upvote it.
Hi @Bobby P ,
Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)
So I indeed have database date [DT_DBDATE] as the Advanced Editor ==> Input and Output Properties ==> Excel Source Output ==> Output Columns [CarrierInvoicedDate] DataType and that doesn't work.
I am adding in a Derived Column task for the [CarrierInvoicedDate] to try and convert MM/DD/YYYY to ISO format YYYY-MM-DD to Data Pump that to a SQL Server Table data column defined as a DATE type with the following Expression...
(DT_DBDATE)(TOKEN( [CarrierInvoicedDate],"/",3) + "-" + TOKEN([CarrierInvoicedDate],"/",1) + "-" + TOKEN([CarrierInvoicedDate],"/",2))
It is still failing on an error. Is my syntax correct? Do I also have to allow if my [CarrierInvoicedDate] has no data? Sooooo do I have to allow for NULLs in this expression and how do I do that.
Also...Do I need to reset that Advanced Editor ==> Input and Output Properties ==> Excel Source Output ==> Output Columns [CarrierInvoicedDate] DataType back to String or something?
Boy...I have pumped a lot of Excel spreadsheets into SQL Server Tables in the past...I just never remember this hassle between Excel format and SQL Server Date format before.
Any help and clarification is GREATLY appreciated.
Can anyone provide a Step-By-Step to do this simple Excel to SQL Server Date Conversion?
Do I need to start a new thread here??