SQL cast

Definition and Usage

The CAST() function converts a value (of any type) into a specified datatype.

Syntax

CAST(expression AS datatype(length))

Questions

1. Write a query to look at the top 10 rows to understand the columns and the raw data in the dataset sf_crime_data.

SELECT *
FROM sf_crime_data
LIMIT 10;

2. Remembering back to the lesson on dates, use the Quiz Question at the bottom of this page to make sure you remember the format that dates should use in SQL

yyyy-mm-dd

3. Look at the date column in the sf_crime_data table. Notice the date is not in the correct format.

The format of the date column is mm/dd/yyyy with times that are not correct also at the end of the date.

4. Write a query to change the date into the correct SQL date format. You will need to use at least SUBSTR and CONCAT to perform this operation.

SELECT date orig_date, (SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) || '-' || SUBSTR(date, 4, 2)) new_date
FROM sf_crime_data;

5. Once you have created a column in the correct format, use either CAST or :: to convert this to date.

SELECT date orig_date, (SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) || '-' || SUBSTR(date, 4, 2))::DATE new_date
FROM sf_crime_data;