Changing The Format Of Data In A Column

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

Solution 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

createview formatted_date_view
asSELECT  ID,STR_TO_DATE(`Date`, '%m/%d/%Y') as'Formatted_Date'FROM    yourtable

(OR)

createtable formatted_date_table
 asSELECT  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

Solution 2:

MS SQL Server Solution:

Which SQL are you trying with?

MSSQL Server 2008 R2

You can use Convert function on your date field. You have to specify the date's format Style. For mm/dd/yyyy format Style value is 101.

Using with style value, your update statement can be:

UPDATE table_name
  SETdate=CONVERT( VARCHAR, date, 101 ) 

Refer To:

• How to format datetime & date in Sql Server
• SQL Server 2008 Date Format
• Demo @ MS SQL Server 2008 Fiddle

---

MySQL Solution:

it needs to stay in varchar or int and the dates are yyyymmdd and I need to change thousands of rows of data to be in mmddyyyy format.

Change to date type using str_to_date and then change again to string using date_format.

UPDATE table_name
  SETdate= DATE_FORMAT( STR_TO_DATE( date, '%Y%m%d' ), '%m%d%Y' )

The value 20140911 when converted from yyyymmdd to mmddyyyy format, will retain the leading 0 as 09112014.

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

You can use str_to_date( '91212', '%c%e%y' ) to convert the same to valid date object. But MySQL, though defines to support single digit month and date numbers, it won't parse such date correctly and returns a NULL on such formats.

mysql>select str_to_date( '91212', '%c%e%y' ) s1, str_to_date( '091212', '%c%e%y' ) s2;
+------+------------+| s1   | s2         |+------+------------+|NULL|2012-09-12|+------+------------+1rowinset, 1 warning (0.00 sec)

mysql>show warnings;
+---------+------+------------------------------------------------------------+| Level   | Code | Message                                                    |+---------+------+------------------------------------------------------------+| Warning |1411| Incorrect datetime value: '91212'forfunction str_to_date |+---------+------+------------------------------------------------------------+1rowinset (0.00 sec)