How to parse a date from text in T-SQL

Question

I already have code for pulling formatted dates from text fields such as the example below:

    SUBSTRING(REPLACE(al.Comments + '.','.','{br}'), 
        PATINDEX('%Visit Date%',REPLACE(al.Comments+'.','.','{br}')) + 13, 
        PATINDEX('%{br}%',
            SUBSTRING(REPLACE(al.Comments+'.','.','{br}'), 
                PATINDEX('%Visit Date%', 
                    REPLACE(al.Comments+'.','.','{br}')) + 13, 14))-1))

The date is inserted programmatically into a Comments field of internal software. My question is how to pull a date from the same table and column (al.Comments) when the comment and the date are input manually by an end user.

Possible ways an end user might input a date would be:

• m/d/yy
• mm/dd/yy
• m/d/yyyy
• mm/dd/yyyy
• m/d (assume the same year as current year)
• mm/d (assume the same year as current year)
• m/dd (assume the same year as current year)
• mm/dd (assume the same year as current year)

Things to note

• This is SQL 2008
• SQL CLR and Regex are not possible
• I cannot request that the software be updated for this process
• I understand that this is complex and it may not be possible to account for all cases so anything that approximates as many of the possible end user inputs will be acceptable

In the case of this date (unlike the sample I posted above) I will be looking for a keyword of 'proposed' followed by a date and any other number of characters. Below is a sample of some of what I see in my results set for al.Comments:

I am creating a tracker in SSRS and among many other tables I am creating a result set that would allow me to track any proposed dates within a week or less of the date proposed. Most of my other result sets are either using datetime stamps or are pulling from programmatically added dates like the sample I posted. I can't figure out a way in SQL to pull this date so I can create a column for my results set/SSRS tracker

EDIT:

I have loosely called this a 'form' when in fact it is internal software that has a dev team that DOES have access to it, but this is part of a process and a database that is complex and has a lot of history. This department is one of many, all of our software outside of standard Google and Microsoft software is created internally. We have dozens of pieces of software that are all inter related and tied to each other in a very large, very complex database. I didn't write the software and the team that is over this software has bigger fish to fry that to add a field for my tracker to work.

This department is experiencing growing pains (a good thing) and just restructured all job functions last month. I work for a reports team and I was assigned a report to develop to track jobs assigned to technicians in the field.

If I have to write a complex, long SUBSTRING I will. Department standards do require that my queries are optimized and very fast. So I was looking for a way to query the database and parse text as simply as possible.

EDIT 2 (Testing Alan Burstein's answer below)

I have been testing Alan's answer and it is close. I could use a little help getting to the finish line. Below are some snippets of test code and the results I am getting.

SELECT
    SUBSTRING(al.Comments,PATINDEX('%proposed%',al.Comments)+9,17)  [col1]
INTO
    #test1

Followed by this code:

SELECT
    CASE
    WHEN col1 LIKE '%/%-%/%' THEN SUBSTRING(#test1.col1,PATINDEX('%/%',#test1.col1)-2,5)
    WHEN col1 LIKE '%/%,%/%' THEN SUBSTRING(#test1.col1,PATINDEX('%/%',#test1.col1)-2,5)
    WHEN col1 LIKE '%/%/%' THEN SUBSTRING(#test1.col1,PATINDEX('%/%/%',#test1.col1)-2,12)
    END
FROM
    #test1

Sample of the results I am getting is below this - I haven't done the CAST yet because I have some leading spaces and trailing text I need to figure out how to exclude - thoughts?

This will be in a temp table and a LEFT JOIN so NULLs are fine.

EDIT 3 - How I got it to work w/o a function

I marked Alan's answer as correct because it worked when tested but I was told no functions by our Data Team Lead. I found another solution on StackExchange Read the second answer by G Mastros. I applied this next query to my previous one to strip out the offending characters:

SELECT
    LEFT(SUBSTRING(#test2.col1, PATINDEX('%[0-9/]%', #test2.col1), 10),
            PATINDEX('%[^0-9/]%', SUBSTRING(#test2.col1, PATINDEX('%[0-9/]%', #test2.col1), 10) + 'X')-1)
FROM #test2

The new results:

Answer 1

I'm thinking:

DECLARE @table TABLE (col1 varchar(20));
INSERT @table VALUES 
('1/2/01'),('11/12/99'),('5/5/2013'),('09/13/2003'),
('2/4'),('12/4'),('8/11'),('12/12');

SELECT *, newvalue = 
  CASE 
    WHEN col1 LIKE '%/%/%' THEN CAST(col1 AS date) 
    ELSE col1+'/'+this.yr
  END

FROM @table
CROSS JOIN (VALUES (CAST(YEAR(getdate()) AS varchar(4)))) this(yr);

Returns:

col1                 yr   newvalue
-------------------- ---- ----------
1/2/01               2018 2001-01-02
11/12/99             2018 1999-11-12
5/5/2013             2018 2013-05-05
09/13/2003           2018 2003-09-13
2/4                  2018 2018-02-04
12/4                 2018 2018-12-04
8/11                 2018 2018-08-11
12/12                2018 2018-12-12

Easy peasy lemon squeezy.

---

UPDATE BASED ON OP'S UPDATE

One easy way to extract the dates would be to use PatternSplitCM; code below:

-- Function by Chris Morris, read more here: http://www.sqlservercentral.com/articles/String+Manipulation/94365/
    CREATE FUNCTION dbo.PatternSplitCM
(
       @List                VARCHAR(8000) = NULL
       ,@Pattern            VARCHAR(50)
) RETURNS TABLE WITH SCHEMABINDING 
AS RETURN
    WITH numbers AS (
      SELECT TOP(ISNULL(DATALENGTH(@List), 0))
       n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
      FROM
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n))

    SELECT
      ItemNumber = ROW_NUMBER() OVER(ORDER BY MIN(n)),
      Item = SUBSTRING(@List,MIN(n),1+MAX(n)-MIN(n)),
      [Matched]
     FROM (
      SELECT n, y.[Matched], Grouper = n - ROW_NUMBER() OVER(ORDER BY y.[Matched],n)
      FROM numbers
      CROSS APPLY (
          SELECT [Matched] = CASE WHEN SUBSTRING(@List,n,1) LIKE @Pattern THEN 1 ELSE 0 END
      ) y
     ) d
     GROUP BY [Matched], Grouper;

Here's some updated sample data with the dates buried in the text along with code to demonstrate how you could extract the "dates":

DECLARE @table TABLE (col1 varchar(100));
INSERT @table VALUES 
('blah blah 1/2/01 xxxx'),('my name is fred and today is: 11/12/99'),
('5/5/2013 is the day I met Fred'),('The due date is 09/13/2003!!!'),
('This little piggy... ((2/4))'),('Call me on 12/4 at 10:30PM'),
('8/11 is the day after August 10th'),('Really?!?! 12/12 is the best?');

SELECT
  t.col1,
  ps.Item
FROM @table t
CROSS APPLY dbo.PatternSplitCM(t.col1,'[0-9/]') ps
WHERE [Matched] = 1 AND Item LIKE '%[0-9]/[0-9]%';

Returns:

col1                                        Item
------------------------------------------- -----------
blah blah 1/2/01 xxxx                       1/2/01
my name is fred and today is: 11/12/99      11/12/99
5/5/2013 is the day I met Fred              5/5/2013
The due date is 09/13/2003!!!               09/13/2003
This little piggy... ((2/4))                2/4
Call me on 12/4 at 10:30PM                  12/4
8/11 is the day after August 10th           8/11
Really?!?! 12/12 is the best?               12/12

Next you turn this into a subquery and apply my original logic like so.

Updated solution:

DECLARE @table TABLE (col1 varchar(100));
INSERT @table VALUES 
('blah blah 1/2/01 xxxx'),('my name is fred and today is: 11/12/99'),
('5/5/2013 is the day I met Fred'),('The due date is 09/13/2003!!!'),
('This little piggy... ((2/4))'),('Call me on 12/4 at 10:30PM'),
('8/11 is the day after August 10th'),('Really?!?! 12/12 is the best?');

SELECT original = col1, newvalue =
  CASE 
    WHEN dt.item LIKE '%/%/%' THEN CAST(dt.item AS date) 
    ELSE dt.item+'/'+this.yr
  END
FROM
    (
  SELECT
    t.col1,
    ps.Item
  FROM @table t
  CROSS APPLY dbo.PatternSplitCM(t.col1,'[0-9/]') ps
  WHERE [Matched] = 1 AND Item LIKE '%[0-9]/[0-9]%'
) dt
CROSS JOIN (VALUES (CAST(YEAR(getdate()) AS varchar(4)))) this(yr);

Returns:

original                                    newvalue
------------------------------------------- ----------
blah blah 1/2/01 xxxx                       2001-01-02
my name is fred and today is: 11/12/99      1999-11-12
5/5/2013 is the day I met Fred              2013-05-05
The due date is 09/13/2003!!!               2003-09-13
This little piggy... ((2/4))                2018-02-04
Call me on 12/4 at 10:30PM                  2018-12-04
8/11 is the day after August 10th           2018-08-11
Really?!?! 12/12 is the best?               2018-12-12