"[Substr] returns a string object with its contents initialized to a substring of the current object."

it will give the substring of length(as per the user) from the actual string,and the starting length from where it has to copy the substring from actual should be given by the user.

No. Longest is a superlative.

Assumptions:

1) the column had a name such as someDate
2) the column is extracted as character rather than date, datetime or the like
3) the days portion of the field always has 2 digits; only the month portion has an optional leading zero, implying the field will always have a length of either 5 or 6

LEN(someDate) == 5 ?
'0' + SUBSTRING(someDate,1,1) + ' ' + SUBSTRING(someDate,2,2) + ' 20' + SUBSTRING(someDate,4,2)
:
SUBSTRING(someDate,1,2) + ' ' + SUBSTRING(someDate,3,2) + ' 20' +
SUBSTRING(someDate,5,2)

The CHARINDEX function in SQL is used to find the position of a specific character or substring within a string. It returns the starting position of the substring or character within the given string.