SQL Server – Using a String with Single Quotes in a Table

Use CONVERT and specify 1 as the date time style 1 = mm/dd/yy.

CONVERT(datetime, RIGHT('CXL P/D 08/15/13', 8), 1)

I use charIndex and patIndex to resolve it.

CASE WHEN PatIndex('%, [0-9][0-9]% Stage%',Descr) > 0 
    AND LEN(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1))>=3
    THEN RIGHT(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1),3) 
    ELSE NULL END
    AS NewNo,   
CASE WHEN PatIndex('%, [0-9][0-9]% Stage%',Descr) > 0 
    AND LEN(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1))>=3
    THEN SUBSTRING(Descr, PatIndex('%, [0-9][0-9]% Stage%',Descr)+2,3)
    ELSE NULL END
    AS NewNo2   

With the output:

NewNo NewNo2
----- ------
 98   98 
111   111
125   125
134   134

Some explanation on it: I used to CharIndex(' Stage', Descr)to find the position when starts ' Stage'.
Then I use SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1) to cut the text so that your number will be in the right part. (like this :Playhouse, Virginia Series, 98).Then you can use different technique (right,another charIndex,reverse) to obtain the Number.