Conversion from nvarchar to an appropriate date format

Hi All,

i have physical table where the data type of the column is nvarchar(15) now i want to convert the date format to yyyy-mm-dd how can i do that ?

declare @date nvarchar(15) = '20/3/2018'

select convert(varchar(10),replace(@date,'/','-'),120)

current output
20-3-2018

expected output
2018-03-20

select convert(varchar(10),replace(@date,'/','-'),23)

@ahmeds08 Hi,

Thanks for reply,

Am trying to get ouput in this format 2018-03-20 but your query returns 20-03-2020.

hi hope this helps

SELECT FORMAT(GetDate(), 'yyyy-MM-dd')

Hi @harishgg1,
Thank you for reply,

declare @date nvarchar(10) = '20/3/2020'

SELECT FORMAT(@date, 'yyyy-MM-dd')

i replaced @date instead of getdate() function am getting below error
Argument data type nvarchar is invalid for argument 1 of format function.

i am using nvarchar(10) since my real table has the same data type and it has been used in multiple places.

declare @date nvarchar(10) = '20/03/2020'

SELECT Convert(Date, @date, 103)

you cannot use functions on NVARCHAR

please cast it as varchar first and then use format

Thank you @mike01 it worked.

@harishgg1

i tried your approach as you told it still return the same error.

declare @date nvarchar(10) = '20/3/2020'

declare @datenew varchar(10)

set @datenew = cast(@date as varchar)

select format(@datenew ,'yyyy-MM-dd')

any ways @mike01 solution was helpful.

Thank you @harishgg1 for your time.

hi vajeed

Format function does not like varchar datatype ..

Format function expects other data Types
which you need to convert nvarchar into before applying the format function

No need for that much depth .. you got your answer .. unless in a precarious situation

1 Like

You should explicitly the convert to format yyyy-mm-dd and not rely on default formatting, like this:

SELECT CONVERT(nvarchar(15), CONVERT(date, your_column_name, 103), 23)

1 Like