0

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
3

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;
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.