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?
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);
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.
DateTime2 (A.D or after 9999), it's better to store the dates as numbers (Like SQL Server does internally) then as strings. Also, I wouldn't recommend datetime but datetime2 - Here's why. All that being said, still this is a solid answer so +1.You can try this:
declare @dt NVARCHAR(12) = '15.03.18'
SELECT CONVERT(DATE,@dt,3)
GO
3 style in format expects / and not . as a date separator. You want to use 4 instead - convert(date, @dt, 4). Even if your code worked, it's only a partial answer at best.
WEEK_DATE, and can you show us sample data?