How to extract the date from this varchar column

Question

I need to remove records from this table that are older than 30 days.

We only have 2 columns in this table. The first date after 'Printed_%' is what I will be using to determine if the record should be deleted. Notice some of the dates are in MM/DD/YYYY format while some appear in M/D/YYYY format in the string. How can extract the date so I can determine which rows are older than 30 days?

ID      Command
123232  Printed_9/6/2017 2:36:09 PM W;03;2K;8/6/2013;8/6/2015;ODSI3498
123233  Printed_9/6/2017 2:53:20 PM W;07;2K;8/6/2013;8/6/2015;PWEOIR123
123234  Printed_9/6/2017 2:54:44 PM W;15;2K;11/9/2011;11/18/2005;PWEOIR123
123235  Printed_10/21/2016 10:21:10 AM W;15;2K;11/9/2011;11/18/2005;ODSI3498
123236  Printed_11/15/2015 10:30:13 AM W;02;2K;8/7/2013;8/7/2014;ODSI3498
123237  Printed_12/7/2014 10:30:26 AM W;21;2K;7/30/2013;7/30/2014;ODSI3498

Answer 1

Since you're on 2012+, you may want to use TRY_CONVERT just in case you run into any odd/malformed dates.

DECLARE @dummy TABLE
(
    id INT,
    command VARCHAR(1000)
);

INSERT  @dummy
VALUES ( 123232, 'Printed_9/6/2017 2:36:09 PM W;03;2K;8/6/2013;8/6/2015;ODSI3498       ' ),
       ( 123233, 'Printed_9/6/2017 2:53:20 PM W;07;2K;8/6/2013;8/6/2015;PWEOIR123      ' ),
       ( 123234, 'Printed_9/6/2017 2:54:44 PM W;15;2K;11/9/2011;11/18/2005;PWEOIR123   ' ),
       ( 123235, 'Printed_10/21/2016 10:21:10 AM W;15;2K;11/9/2011;11/18/2005;ODSI3498 ' ),
       ( 123236, 'Printed_11/15/2015 10:30:13 AM W;02;2K;8/7/2013;8/7/2014;ODSI3498    ' ),
       ( 123237, 'Printed_12/7/2014 10:30:26 AM W;21;2K;7/30/2013;7/30/2014;ODSI3498   ' );

SELECT  *,
       TRY_CONVERT(DATETIME,
            SUBSTRING(
            d.command,
            LEN('Printed_') + 1,
            CHARINDEX(' ', d.command, LEN('Printed_') + 1)
                    ) 
                ) AS command_date
FROM    @dummy AS d;

Answer 2

You could avoid first 9 characters (Printed_) and remove the last 2 character until the first ';'

[Printed_][9/6/2017 2:36:09 PM][ W]; 

CREATE TABLE T(ID INT, COMMAND VARCHAR(MAX));
INSERT INTO T VALUES
(123232,  'Printed_9/6/2017 2:36:09 PM W;03;2K;8/6/2013;8/6/2015;ODSI3498'),
(123233,  'Printed_9/6/2017 2:53:20 PM W;07;2K;8/6/2013;8/6/2015;PWEOIR123'),
(123234,  'Printed_9/6/2017 2:54:44 PM W;15;2K;11/9/2011;11/18/2005;PWEOIR123'),
(123235,  'Printed_10/21/2016 10:21:10 AM W;15;2K;11/9/2011;11/18/2005;ODSI3498'),
(123236,  'Printed_11/15/2015 10:30:13 AM W;02;2K;8/7/2013;8/7/2014;ODSI3498'),
(123237,  'Printed_12/7/2014 10:30:26 AM W;21;2K;7/30/2013;7/30/2014;ODSI3498');
GO

6 rows affected

SELECT ID, 
       CAST(SUBSTRING(COMMAND, 9, CHARINDEX(';', COMMAND) - 11) AS DATETIME) AS DT
FROM   T
GO

    ID | DT                 
-----: | :------------------
123232 | 06/09/2017 14:36:09
123233 | 06/09/2017 14:53:20
123234 | 06/09/2017 14:54:44
123235 | 21/10/2016 10:21:10
123236 | 15/11/2015 10:30:13
123237 | 07/12/2014 10:30:26

dbfiddle here