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.

Answers


Most SQL engines have a substring() function. Use that together with concat() to create the new string:

(pseudocode)

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.


SELECT CONVERT(DATE,CONVERT(DATEtime,'01/06/2012',103))

For more examples Click here


select cast(cast('01/06/2012' as DATE) as varchar(10))

select convert(datetime,'01/06/2012',103)

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.


Need Your Help

Java Calendar.roll and CST during daylight saving changes

java datetime timezone dst

I wonder if Calendar.roll respects its javadoc contract:

How to add UIDatePicker in UIAlertController in iOS8?

ios uidatepicker uiactionsheet uipicker uialertcontroller

I am working on a project which I had already released with iOS-7. But now as the action sheet is making problem so I am now implementing UIAlertController. Following is the code which I am using for