CODEWITHSUNDEEP
sqlsql-serverstringt-sqldate
CA_CA
CA_CA

Reputation: 143

Error when converting varchar to date ddmmyyyy

I have a varchar column with the following format ddmmyyyy and I'm trying to convert it to date in the format dd-mm-yyyy. I'm using the query below but I get the error:

Conversion failed when converting date and/or time from character string.

select *, coalesce(try_convert(date, newdate, 105), convert(date, newdate)) 
from mydate

Upvotes: 1

Views: 507

Answers (4)

MattM
MattM

Reputation: 384

SQL doesn't store date data types in different formats, and it's probably not a good idea to try and adjust this.

If, however, you are wanting a result set to simply display the date in a different format, you are on the right track. You just need to convert your date data type to a string.

SELECT  *
        , COALESCE ( TRY_CONVERT ( CHAR(10), newdate, 105 ), CONVERT ( CHAR(10), newdate ) ) 
FROM    mydate

Upvotes: 0

anon
anon

Reputation:

Have to agree with the others. Why are you storing a date as a string in the first place? In a non-standard format, no less? Here's one way, but you should really fix the data model. Store dates as dates.

DECLARE @badIdea table (dt char(8));

INSERT @badIdea(dt) VALUES('21052020');

SELECT newdate = TRY_CONVERT(date, RIGHT(dt,4) + SUBSTRING(dt,3,2) + LEFT(dt,2))
  FROM @badIdea;

BTW 105 won't work because it requires dashes. This works:

SELECT CONVERT(date, '21-05-2020', 105);

That's a bad format too, IMHO, because who knows if 07-08-2020 is July 8th or August 7th. But at least that one is supported by SQL Server. Your current choice is not.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You don't have a date, you have a string. So, you can use string operations:

select stuff(stuff(newdate, 5, 0, '-'), 3, 0, '-')

If you want to convert to a date, you can do:

select convert(date, concat(right(newdate, 4), substring(newdate, 3, 2), left(newdate, 2)))

You could then format this as you want.

However, you should not be converting the value to a date. You should be storing it as a date in the first place.

Upvotes: 1

GMB
GMB

Reputation: 222462

To turn your string to a date, you can just [try_]cast() it; SQL Server is usually flexible enough to figure out the format by itself:

try_cast(newdate as date)

If you want to turn it back to a string in the target format, then you can use format():

format(try_cast(newdate as date), 'dd-MM-yyyy')

Compared to pure string operations, the upside of the try_cast()/format() approach is that it validates that the string is a valid date in the process.

Upvotes: 0

Related Questions