How to separate this field into two columns

Question

Hello again fellow sql'rs,

I've got a seamingly simple problem to solve that normally would be fairly easy. I've got a field that contains a DateTime portion, as well as a trailing text portion. I now need to split this field into two discrete fields - DateTime and Varchar. Now for the little gotcha. The data has been saved with two different date formats which has resulted in the filed looking a 'lot' like this:

amendmentnote
----------------------------------------------------------------------
30/07/2010 11:39:55: Booking status change from On Option to Cancelled
5/5/2010 10:1:8 : New

as you can see, the dates are in two completely different formats. I'd like to somehow see it parsed out as:

dateofnote          | note
----------------------------------------------------------------------
30/07/2010 11:39:55 | Booking status change from On Option to Cancelled
05/05/2010 10:01:08 | New

is this easily do-able??

cheers jim

Answer 1

It's do-able, but it'll be ugly.

You can use string functions to find the third colon in the amendmentnote field, and anything to the right of the third colon will be your note.

As for the date, you should again be able to use string functions to reformat the date portion, although you'll most likely need lots of substrings to make it work.

My only concern would be if the date formats entered are MM/DD/YYYY for one entry, and DD/MM/YYYY for the other.

Answer 2

Once in a DateTime column, they'll be in the standard DateTime format. How they're presented once queried at that point is up to you.

So, once you split your data into your DateOfNote and Note columns, you can Convert the DateOfNote to VarChar and apply a format to get what you want.
Convert(NVARCHAR, DateOfNate, 103) will get you there (I think: double check the format style there at the end).

Edit Based on your question, it looks like you wanted more help with the formatting. However, on the splitting the column, you'll need to use string functions. I'd find the index of that last colon, store it in a local variable, and then use substring to find the datetime (left of that last colon) and the note (right of last colon).

Answer 3

Easily? No. Do-able. Yes, if we can make some assumptions. If it is the case that the text never contains a colon, you could do:

Declare @Data Table ( Data Varchar(max) )
Insert @Data(Data) Values('30/07/2010 11:39:55: Booking status change from On Option to Cancelled')
Insert @Data(Data) Values('5/5/2010 10:1:8 : New')

Set DateFormat DMY

Select Cast(Reverse(Substring(Reverse(Data), CharIndex(':', Reverse(Data)) + 1, Len(Data))) As DateTime)
    , LTrim(Reverse(Substring(Reverse(Data), 1, CharIndex(':', Reverse(Data)) - 1)))
From @Data

Answer 4

Based on what's provided, use:

SELECT CONVERT(DATETIME, 
               SUBSTRING(t.amendmentnote, 1, LEN(SUBSTRING(t.amendmentnote, 1, PATINDEX('%: %', t.amendmentnote)))-1), 
               103),
       LTRIM(SUBSTRING(t.amendmentnote, 
                       LEN(SUBSTRING(t.amendmentnote, 1, PATINDEX('%: %', t.amendmentnote)))+1, 
                       LEN(t.amendmentnote)))  
  FROM YOUR_TABLE t

Being a DATETIME, you can use CAST/CONVERT to format it as you like - don't store "presentation" data.

Bad data is bad data - this is a mine field you'll have to navigate, isolating rows that won't match the pattern in the query & deal with appropriately.