vajeed
1
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)
vajeed
3
@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')
vajeed
5
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.
mike01
6
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
vajeed
8
Thank you @mike01 it worked.
vajeed
9
@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