Change varchar to date format?

Use datetime.parseexact

http://www.dotnetperls.com/datetime-parse[^]

In T-SQL 2014 :

DECLARE @d DATETIME = GETDATE()

SELECT FORMAT( @d, 'yyyy/MM/dd', 'en-US' ) AS 'DateTime Result'

From SQL server 2012, you can use Try_Parse method[^]:

SELECT convert(varchar ,TRY_PARSE('Wednesday, June 04, 2014' AS date), 111)

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.

/****** Object:  UserDefinedFunction [dbo].[fn_GetDateFromVarchar]    Script Date: 06/26/2014 15:14:44 ******/
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


/****** Object:  UserDefinedFunction [dbo].[fn_GetDateFromVarchar]    Script Date: 06/26/2014 15:14:45 ******/
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