February 15, 2008 at 9:15 am
Hi,
I am importing data from an Excel spreadsheet, but one field is causing a problem when it is converted to a date in a staging table in my server.
The field is a text field in Excel, with entries such as the following
"20070327" & "20070308"
The data is imported then converted initially to a unicode string [dt_wtsr] using a data conversion editor, then in a Derived column transformation editor i use the following calc to convert to a date format for the sql destination table.
(DT_DATE)(SUBSTRING(INVOICEDATE,7,2) + "/" + SUBSTRING(INVOICEDATE,5,2) + "/" + SUBSTRING(INVOICEDATE,1,4))
Now it works fine if I run the import procedure in BIDS, but when run through the sql agent as a scheduled job, the date format does not convert successfully. Instead some convert correctly to (in this example) to 27/032008, but when the final 2 digits of the string are less than 12 (such as in the second example, the date is converted to 03/08/2007 which is incorrect.
so, is there a more efficient way of converting the data in excel to a date format, or what am I doing wrong here to cause the error?
Thx for your help
February 15, 2008 at 3:41 pm
The best way to send the date to SQL Server is YYYY/MM/DD. So if you switch this:
SUBSTRING(INVOICEDATE,7,2) + "/" + SUBSTRING(INVOICEDATE,5,2) + "/" + SUBSTRING(INVOICEDATE,1,4)
to this:
SUBSTRING(INVOICEDATE,1, 4) + "/" + SUBSTRING(INVOICEDATE,5,2) + "/" + SUBSTRING(INVOICEDATE,7,2))
It should work. I have tested it myself. You also do not need to do the conversion to date in SSIS, SQL Server will do it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2008 at 1:49 am
Thx for that - my ssis package also runs faster now without the date conversion.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply