This seems an interesting Problem and no direct solution present any where. I have developed first version of a function that could parse the input Date String and return a valid datetime. I have intentionally made it simple without doing any optimizations as of now and understand that it has issues like it is not taking care of Locales for example.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetDateFromVarchar]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetDateFromVarchar]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Syed Asif Iqbal
-- Create date: 26-Jun-2014
-- Description: VARCHAR to DateTime Conversion
-- =============================================
CREATE FUNCTION [dbo].[fn_GetDateFromVarchar]
(
@V_INPUTDATE VARCHAR(50)
)
RETURNS DATETIME
AS
BEGIN
DECLARE @TMPDATE VARCHAR(100)
SELECT @TMPDATE = REPLACE(@V_INPUTDATE, ',', '')
DECLARE @TBLDAY TABLE
(
DAYID INT,
DAYN VARCHAR(10)
)
DECLARE @TBLMONTH TABLE
(
MONTHID INT,
MONTHN VARCHAR(10)
)
WITH R(N) AS
(
SELECT 0
UNION ALL
SELECT N+1
FROM R
WHERE N < 6
)
INSERT INTO @TBLDAY (DAYID, DAYN)
SELECT N+1, DATENAME(DW,DATEADD(DAY,N,'01-Jan-1900'))
FROM R;
SELECT @TMPDATE = REPLACE(@TMPDATE, DAYN+' ', '')
FROM @TBLDAY
WITH R(N) AS
(
SELECT 0
UNION ALL
SELECT N+1
FROM R
WHERE N < 11
)
INSERT INTO @TBLMONTH (MONTHID, MONTHN)
SELECT N+1, DATENAME(MONTH,DATEADD(MONTH,N,'01-Jan-1900'))
FROM R
SELECT @TMPDATE = REPLACE(@TMPDATE, MONTHN, MONTHID)
FROM @TBLMONTH
SELECT @TMPDATE = REPLACE(@TMPDATE, ' ', '/')
-- Return the result of the function
RETURN CAST(@TMPDATE as datetime)
END
GO
Below script can be used to test the function
DECLARE @TBL TABLE
(
DAYID INT,
VarcharDate VARCHAR(50)
)
WITH R(N) AS
(
SELECT 0
UNION ALL
SELECT N+1
FROM R
WHERE N < 6
)
INSERT INTO @TBL
(DAYID, VarcharDate)
SELECT N+1, DATENAME(DW,DATEADD(MONTH,N,GETDATE())) + ', ' + DATENAME(MONTH,DATEADD(MONTH,N,GETDATE())) + ' ' + DATENAME(DD, DATEADD(MONTH,N,GETDATE())) + ', ' + DATENAME(YYYY, GETDATE())
from R;
WITH R(N) AS
(
SELECT 0
UNION ALL
SELECT N+1
FROM R
WHERE N < 6
)
INSERT INTO @TBL
(DAYID, VarcharDate)
SELECT N+1, DATENAME(DW,DATEADD(DAy,N,GETDATE())) + ', ' + DATENAME(MONTH,DATEADD(DAy,N,GETDATE())) + ' ' + DATENAME(DD, DATEADD(DAy,N,GETDATE())) + ', ' + DATENAME(YYYY, GETDATE())
from R
select VarcharDate, dbo.fn_GetDateFromVarchar(VarcharDate)
from @TBL