CODEWITHSUNDEEP
sqlsql-servert-sql
MSM
MSM

Reputation: 357

CASTing and converting functions in SQL Server

I am trying to convert dd.mm.yyyy to yyyy-mm-dd.

select convert(date,CAST(WEEK_DATE as nvarchar(220)), 120) 
from z_fact

Error

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

How can I resolve this?

Upvotes: 1

Views: 101

Answers (2)

Aswani Madhavan
Aswani Madhavan

Reputation: 816

You can try this:

declare @dt NVARCHAR(12) = '15.03.18' 
SELECT  CONVERT(DATE,@dt,3)
GO

Upvotes: -1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522636

Since your date is actually text, you must first convert it to a bona fide date using CONVERT. Then, use CONVERT on that date a second time to generate the text output you want.

SELECT CONVERT(varchar(20), CONVERT(datetime, '15.03.18', 4), 120);

Demo

Note that it is generally bad practice to store your dates as text. Hopefully you can use my answer to tidy up your table. For example, you could add a new datetime column new_dt and then update it using:

UPDATE yourTable
SET new_dt = CONVERT(datetime, old_dt, 4);

Don't worry about the internal format used by SQL Server. If you still need to display yyyy-mm-dd output, then use CONVERT again, as I did in my first query.

Upvotes: 3

Related Questions