Split Column based on Delimiter

Question

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

Answer 1

0

select startdate= ltrim(substring([ColumnA],1,charindex('-',[ColumnA])-1))
select enddate= ltrim(substring([ColumnA],charindex('-',[ColumnA])+1,len([ColumnA])))

answered Jul 24 '12 at 09:43 PM

sunil2711
196 ● 1 ● 2 ● 6

(comments are locked)

Answer 2

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.

Split Column based on Delimiter

Follow this question

Related Questions