CODEWITHSUNDEEP
sqlsql-serverdatesql-server-2012
good-to-know
good-to-know

Reputation: 742

Convert VARCHAR to DATE in SQL SERVER

I have VARCHAR column (MyValue) in my table. It has date value in two different format. 

MyValue
----------
25-10-2016
2016-10-13

I would like to show them in DATE format.

I wrote query like below:

SELECT CONVERT(date, MyValue, 105) FROM MyTable
SELECT CAST(MyValue as date) FROM MyTable

Both are giving me this error. Conversion failed when converting date and/or time from character string.

Is there anyway convert to DATE datatype format even the value stored in different formats like above?

Expecting your answers. Thanks in advance.

Upvotes: 1

Views: 10524

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15140

You can use TRY_CONVERT and COALESCE. TRY_CONVERT returns NULL if the conversion fails, COALESCE returns the first NOT NULL value:

SELECT  COALESCE(TRY_CONVERT(DATETIME, x, 105), TRY_CONVERT(DATETIME, x, 120))
FROM    (VALUES('25-10-2016'), ('2016-10-13')) a(x)

I assumed the value 2016-10-13 is in format yyyy-MM-dd.

You mention in a comment you may have other formats as well. In that case it gets very tricky. If you get a value 01-12-2017 and you have no idea about the format, there is no way to tell whether this is a date in januari or in december.

Upvotes: 2

Jonathan Willcock
Jonathan Willcock

Reputation: 5245

Does this help?

declare @varchardates table
(
vcdate varchar(20)
)

INSERT INTO @varchardates VALUES
('25-10-2016'),
('2016-10-13')

SELECT CONVERT(date,vcdate, case when SUBSTRING(vcdate, 3, 1) = '-' 
THEN 105 ELSE 126 END) as mydate 
FROM @varchardates

Depending on how many different formats you have in your data, you may need to extend the case statement!

See here for list of the different format numbers

Upvotes: 2

Related Questions