question

SQL USER avatar image
SQL USER asked

get string between certain characters //

i'm loading data from excel to sql and i have the date in a cell like this '10/2/14 to 10/2/14'. how do i get the date only and insert to datetime column in sql table. i tried to substring but couldnt figure out how to get the day from between the / / help plz. thanks,
ssisstringsubstringcharacters
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.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
0 Likes 0 ·
Oleg avatar image
Oleg answered
Usually, it is best if the formatting is done in Excel itself before loading the data, but if you need to do it in T-SQL for whatever reason then you can try to discard the ' to ' part and use the values to the left and to the right of it. If your dateformat setting is m/d/y (default) then the following should do it: select cast(substring(ExcelColumn, 1, charindex(' to ', ExcelColumn) - 1) as datetime) FirstDate, cast(substring(ExcelColumn, charindex(' to ', ExcelColumn) + 4, 50) as datetime) SecondDate from YourTable This way, you don't have to worry about how the day and month part is spelled out, the CAST will cast it regardless whether there is a leading 0 or not. Hope this helps. Oleg
2 comments
10 |1200

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

SQL USER avatar image SQL USER commented ·
thanks, but how can i write this in a derived column in ssis. i have the date from excel '10/2/14 to 10/2/14' stored in a string variable thanks,
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@SQL USER You will need to use the SSIS expressions. I believe that substring is named the same, and the charindex in T-SQL corresponds FINDSTRING.
0 Likes 0 ·
sdoubleday avatar image
sdoubleday answered
You can make creative use of FINDSTRING, LEN, and SUBSTRING. FINDSTRING in SSIS acts much like CHARINDEX in T-SQL, and LEN will give you the total length, from which you can subtract various numbers of characters based on FINDSTRING (such as to find the end of the day elements). An example of this practice in action: http://stackoverflow.com/questions/18875997/ssis-how-to-get-part-of-a-string-by-separator-using-derived-column Though I'd suggest doing all of the manipulation in one derived column in the end, you may find it easier to assemble the logic using multiple derived columns one after the other to separate your dates, then to pull out the month, the day, and the year, and finally to reassemble them in yyyy-MM-dd and cast them to DT_DBDate or similar.
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.