sql - Changing the format of data in a column

Question

Trying the change the date column from YYYYMMDD to MMDDYYYY while maintaining varchar value. Currently my column is set as varchar(10). Is there a way to change the strings in mass numbers because I have thousands of rows that need the format converted.

For example:

|   ID    |    Date    |
------------------------
|    1    | 20140911   |
|    2    | 20140101   |
|    3    | 20140829   |

What I want my table to look like:

|   ID    |    Date    |
------------------------
|    1    | 09112014   |
|    2    | 01012014   |
|    3    | 08292014   |

Bonus question: Would it cause an issue while trying to convert this column if there is data such as 91212 for 09/12/2012 or something like 1381 which is supposed to be 08/01/2013?

Answer 1

Instead of storing the formatted date in separate column; just correct the format while fetching using STR_TO_DATE function (as you said your dates are stored as string/varchar) like below. Again, as other have suggested don't store date data as string rather use the datetime data type instead

SELECT  STR_TO_DATE(`Date`, '%m/%d/%Y')
FROM    yourtable

EDIT:

In that case, I would suggest don't update your original table. Rather store this formatted data in a view or in a separate table all together like below

create view formatted_date_view
as
    SELECT  ID,STR_TO_DATE(`Date`, '%m/%d/%Y') as 'Formatted_Date'
    FROM    yourtable

(OR)

create table formatted_date_table
 as
SELECT  ID,STR_TO_DATE(`Date`, '%m/%d/%Y') as 'Formatted_Date'
FROM    yourtable

EDIT1:

In case of SQL Server use CONVERT function like CONVERT(datetime, Date,110). so, it would be (Here 110 is the style for mm-dd-yyyy format)

    SELECT  ID,convert(datetime,[Date],110) as 'Formatted_Date'
    FROM    yourtable

(OR)

CAST function like below (only drawback, you can't use any specific style to format the date)

    SELECT  ID, cast([Date] as datetime) as 'Formatted_Date'
    FROM    yourtable