question

sqlLearner 1 avatar image
sqlLearner 1 asked

Split Column based on Delimiter

I need to spilt a date into two columns based on the HYPHEN Any suggestions? EXAMPLE 6/13/2012-6/12/2013 It works for EndDate but not for StartDate select StartDate = substring([ColumnA], charindex('-',[ColumnA])-10 , len([ColumnA])) EndDate = substring([ColumnA], charindex('-',[ColumnA])+1 , len([ColumnA])) FROM TABLEA
tsqldelimited-string
10 |1200

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

sunil2711 avatar image
sunil2711 answered
select startdate= ltrim(substring([ColumnA],1,charindex('-',[ColumnA])-1)) select enddate= ltrim(substring([ColumnA],charindex('-',[ColumnA])+1,len([ColumnA])))
10 |1200

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

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Why do you store two dates in one column? If possible, you should change your data model so you store the two dates in separate columns. Also store them in date or datetime columns, not as text. That will ensure you have proper dates stored. If the two dates are collected from a text field in an app or so, you should use the application code to split the string and collect the two separate dates. String manipulation is quicker and more sophisticated in other languages than T-SQL. If the two dates are stored like that in an external source that you are loading from, I suggest using SSIS to transform them into two different columns. As far as the solution using T-SQL, I think sunil2711 is there, or at least close enough for you to work it out on your own.
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.

It's not my data model it was data that we received. I agree thougha very poor data model
0 Likes 0 ·

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.