i am pulling vertical delimited txt file using ssis to the data warehouse. the date data type fields are being written NULL on the table. they're formatted as ddmmyyyy 11241986. i am using derived column but i am getting the error below.
script:
SUBSTRING([OUTD-OPEN-DATE],1,2) + "/" + SUBSTRING([OUTD-OPEN-DATE],3,2) + "/" + SUBSTRING([OUTD-OPEN-DATE], 5,4)
error message:
Your data looks to already be a date given that error message not a string.
"SUBSTRING is not supported on DATE"
Looks like you may have to look at what you are doing to the column before it hits that component if it is actually a string or not as you may have converted it in the connection manager column mappings or a data conversion step previously.
Also format the dates as an ISO standard YYYY-MM-DD this will negate any confusion to SQL and the readers as ISO format is universal.
@anthony.green it's trying to convert it on the DB side since it's Datetime datatype. it worked when i tested it as Varchar but it's not the right way.
it should work if i add CAST right? i tried to write it different way but am not getting it.
any idea would help. thanks!
SUBSTRING([OUTD-OPEN-DATE],1,2) + "-" + SUBSTRING([OUTD-OPEN-DATE],3,2) + "-" + SUBSTRING([OUTD-OPEN-DATE],5,4)
i wanted to continue on this tread. i decided to use a stored procedure to copy it to the main table from the import table. i am getting the dates data as varchar on the import table and convert it to date on the stp query.
example:
CONVERT(varchar(20),(SUBSTRING(Open_Date,1,2) + '-' + CONVERT(varchar(20),(SUBSTRING(Open_Date,3,2) + '-' + CONVERT(varchar(20),(SUBSTRING(Open_Date,5,4))
CAST((SUBSTRING(start_Date,1,2) + '-' + SUBSTRING(start_Date,3,2) + '-' + SUBSTRING(start_Date,5,4))) AS start_Date
i tried with both convert and cast and i am getting this error message. the message is for CAST
Msg 1035, Level 15, State 10, Procedure stpLoanServ_Credit_Bureau_Record_Final_TBELAY, Line 136 [Batch Start Line 2]
Incorrect syntax near 'CAST', expected 'AS'.
i load a csv file to a database using SSIS. but the CSV file has more column than the database. after loading it to the DB i see on the last column it grabbed the rest of the columns record from the csv file. here on the screenshot, the records I want is the number '8' the rest of the records are from the another columns. any help is very appreciated
15 People are following this question.
