CODEWITHSUNDEEP
sql-servert-sqldatesql-update
knahs
knahs

Reputation: 75

Tsql date column update

I have a date column that is in yyyy-mm-dd I want to convert it to dd/mm/yyyyy. The data type for this column is nvarchar(20) .I am willing to change the data type. The query i tried is

Update table1
set Column1= Convert(nvarchar(10),column1,101).

It is executing but is not making any change.

Upvotes: 0

Views: 203

Answers (2)

John Woo
John Woo

Reputation: 263723

You need to convert the value twice,

Update table1 
set Column1 = CONVERT(nvarchar(10),CONVERT(datetime, REPLACE(column1,'-','.'), 102), 103)

Upvotes: 0

paul
paul

Reputation: 22001

Your data is not really a date. It is currently a string, so Convert won't have any effect.

You would need to:

update table1 set Column1 = Convert(nvarchar(10), Convert(datetime, column1), 101)

but better still, you should consider changing the column data type to datetime, then converting it to the desired format when you select data from the table.

Upvotes: 3

Related Questions