x

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
more ▼

asked Jul 24, 2012 at 08:35 PM in Default

sqlLearner 1 gravatar image

sqlLearner 1
792 36 39 46

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
select startdate= ltrim(substring([ColumnA],1,charindex('-',[ColumnA])-1))
select enddate= ltrim(substring([ColumnA],charindex('-',[ColumnA])+1,len([ColumnA])))
more ▼

answered Jul 24, 2012 at 09:43 PM

sunil2711 gravatar image

sunil2711
256 7 8 11

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jul 24, 2012 at 10:09 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

It's not my data model it was data that we received. I agree thougha very poor data model
Jul 25, 2012 at 02:00 PM sqlLearner 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x292
x10

asked: Jul 24, 2012 at 08:35 PM

Seen: 1421 times

Last Updated: Jul 25, 2012 at 02:00 PM