SQL how to change string from eg. 01/06/2012 to 2012-06-01
I have a table table_a with the column field_b which format is string.
In that field their is written e.g. 01/06/2012.
I need to change it to 2012-06-01
I cannot change fields type etc.
Most SQL engines have a substring() function. Use that together with concat() to create the new string:
update table_a set field_b=concat(substring(field_b, 6, 4), '-', substring(field_b, 3, 2), '-', substring(field_b, 0, 2))
Refer to your SQL server's manual for details.
For more examples Click here
select cast(cast('01/06/2012' as DATE) as varchar(10))
update [YourDB].[dbo].[YourTable] set [YourColumn] = convert(nvarchar(10), convert(datetime,[YourColumn]), 126)
This is the answer that will work and will avoid localization issues:
select cast(cast(convert(DATETIME, '01/06/2012', 103) as DATE) as NVARCHAR(10))
You need to use 103 as format, then convert it to date (to cut off the time) and then convert it to varchar.