I am trying to get the start date ONLY from the column below using a SUBSTRING. Can someone tell me what I'm doing wrong?

This is what I have so far:

DECLARE @STR AS VARCHAR(1000) = 'CustomerID #101010 added. Fixed Price: 3.5555 Effective from 10/16/2024 to 03/31/2 SADFGZFGDA025 Deliveries: 100 Company Pricing Program: TEST 1 Pricing Plan Name: Test plan' 

SELECT 
     CASE 
         WHEN CHARINDEX('Effective from ', @STR) > 0 
              AND CHARINDEX(CHAR(10), @STR, CHARINDEX('Effective from ', @STR)) > 0 
             THEN SUBSTRING(@STR, CHARINDEX('Effective from ', @STR) + LEN('Effective from ') + 1, CHARINDEX(CHAR(10), @STR, CHARINDEX('Effective from ', @STR) + LEN('Effective from ') + 1) - (CHARINDEX('Effective from ', @STR) + LEN('Effective from ') + 1)) 
          WHEN CHARINDEX('Effective from ', @STR) > 0 
              THEN SUBSTRING(@STR,CHARINDEX('Effective from ', @STR) + LEN('Effective from ') + 1, 60) 
          ELSE NULL 
     END 'Start Date'

I'm only trying to see 10/16/2024, so I need the substring to end before the to.

Firstly, you have code like

CHARINDEX(CHAR(10), @STR)

In several places. While it (curiously) does not raise an error, that first parameter is not a particularly valid string to search for, so that function call will always return 0. This means your first WHEN Statement will always be ignored.

The second WHEN is structured to start extracting characters from the correct point in the string, but then it reads an arbitrary 60 characters, as opposed to what you actually want.

The following shows one way to extract the desired value. I broke it into two parts for clarity—certainly it could all be munged together into a single statement, but things like this get hard to read very quickly. Note that this does not factor in error handling—it assumes that there will be a valid date there, which I would not count on given the “provided “to” date value shown.

DECLARE @STR AS VARCHAR(1000) = 'CustomerID #101010 added. Fixed Price: 3.5555 Effective from 10/16/2024 to 03/31/2 SADFGZFGDA025 Deliveries: 100 Company Pricing Program: TEST 1 Pricing Plan Name: Test plan' 

DECLARE
  @SubString1     varchar(1000)
 ,@EffectiveFrom  date

--  The string, starting at the date to be extracted
SET @SubString1 = SUBSTRING(@STR, CHARINDEX('Effective from ', @STR) + 15, 1000)

--PRINT @SubString1
--PRINT charindex(' to ', @Substring1)

--  Extract the date
SET @EffectiveFrom = left(@Substring1, charindex(' to ', @Substring1))

PRINT @EffectiveFrom