Click here to Skip to main content
15,849,594 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have this date format: Wednesday, June 04, 2014 (in varchar), and I want to convert it to datetime changing to this format 2014/06/01. How can I do it?
Posted

SQL
/*For yyyy/mm/dd format*/
SELECT CONVERT(VARCHAR(10),@DateTime ,111) AS Date
 
Share this answer
 
Use datetime.parseexact

http://www.dotnetperls.com/datetime-parse[^]
 
Share this answer
 
In T-SQL 2014 :

SQL
DECLARE @d DATETIME = GETDATE()

SELECT FORMAT( @d, 'yyyy/MM/dd', 'en-US' ) AS 'DateTime Result'
 
Share this answer
 
v2
Comments
Cuculala 17-Jun-14 6:57am    
FORMAT is not a recognnizedbuilt-in function name
From SQL server 2012, you can use Try_Parse method[^]:
SELECT convert(varchar ,TRY_PARSE('Wednesday, June 04, 2014' AS date), 111)
 
Share this answer
 
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

SQL
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900