I receive an Excel spreadsheet monthly with a cell containing both comments and a date in the same cell. I import the spreadsheet to a SQL table and need to extract the last 8-10 characters (the date) and convert them from NVARCHAR to DATE format.
Example:
From this: Active/No Address 4/11/2016
To this: 2016/04/11
The biggest challenge I'm having is that the numbers representing the month and date are not fixed length, they could be either one or two characters. Single character days or months do not have a leading zero.
I have tried every combination of CHARINDEX, RIGHT(), LEFT(), REPLACE, and SUBSTRING I could think of without any success.
Assistance with this would be greatly appreciated.
The method I would go with is reversing the string, finding the first space, and then formatting that chunk of text. This code works for multiple cases with single value months and days and it will not be impacted by the length of the text before the date.
Declare @1 VARCHAR(50) = 'Active/No Address 4/11/2016'
Declare @2 VARCHAR(50) = 'Active/No Address 9/3/2016'
Declare @3 VARCHAR(50) = 'Active/No Address 12/31/2016'
SELECT
REPLACE(CONVERT(DATE, RIGHT(@1,CHARINDEX(' ',REVERSE(@1),0)), 110), '-','/')
, REPLACE(CONVERT(DATE, RIGHT(@2,CHARINDEX(' ',REVERSE(@2),0)), 110), '-','/')
, REPLACE(CONVERT(DATE, RIGHT(@3,CHARINDEX(' ',REVERSE(@3),0)), 110), '-','/')