question

Tsegish avatar image
Tsegish asked

converitng string to date data type

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:

1635870469414.png

derived-column
1635870469414.png (38.3 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thanks Anthony. it worked.

0 Likes 0 ·
Tsegish avatar image
Tsegish answered

@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)

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Let the application format the date the correct way, dates are dates you really shouldn’t be changing them to strings in SQL as they wont behave well if you use them elsewhere.





0 Likes 0 ·
Tsegish avatar image
Tsegish answered

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'.


10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tsegish avatar image
Tsegish answered

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


1638975980085.png


1638975980085.png (14.0 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.