ITM 304 DeVry University Managing Data Structures Worksheet

Week 9 Homework 2 Convert SalesDate to data type DATE and Price to INT

I. Update data types of re_Sales in your database making Sales_Date as DATE and Price as Number.

Step 1 :

Set data in the correct format ( update, use substr() and str_to_date() , replace() )

Example : Convert to default database format:

OR use str_to_date() and replace()

SELECT str_to_date(replace(replace(sale_date,”00:00:00 EDT “,”),'”‘,”) ,’%W %M %d %Y‘)
FROM re_Sales;

Step 2

Update re_Sales set SalesDate =

OR

Update re_Sales set SalesDate =
str_to_date(replace(replace(sale_date,”00:00:00 EDT “,”),'”‘,”) ,’%W %M %d %Y’);

Step 3:

Alter table re_Sales change SalesDate SalesDate Date;

OR

Alter table re_Sales modify SalesDate Date;

II. Use Replace() to get rid of double quotes in Price data : replace(price,'”‘,” )

a. update re_Sales set price = replace(price,'”‘,” );
alter table re_Sales change price price INT;