Using CONVERT()

The CONVERT() function can help you to convert dates into the desired format.

You need to get a report of the airports, carriers, canceled flights, and registration dates, registered in the first six months of the year 2014. You realize that the format of the registration_date column is yyyy-mm-dd, and you want to show the results in the format of mm/dd/yyyy, which is hardcoded as 101, using the CONVERT() function.

Notice that the type of the registration_date column is VARCHAR(10) and not a date.

This exercise is part of the course

Cleaning Data in SQL Server Databases

View Course

Exercise instructions

Convert the type of the registration_date column to DATE and print it in mm/dd/yyyy format.
Convert the registration_date column to mm/dd/yyyy format to filter the results.
Filter the results for the first six months of 2014 in mm/dd/yyyy format.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

SELECT 
    airport_code,
    carrier_code,
    canceled,
    -- Convert the registration_date to a DATE and print it in mm/dd/yyyy format
    ___(VARCHAR(10), ___(registration_date AS DATE), 101) AS registration_date
FROM flight_statistics 
-- Convert the registration_date to mm/dd/yyyy format
WHERE ___(VARCHAR(10), ___(registration_date AS DATE), 101) 
	-- Filter the first six months of 2014 in mm/dd/yyyy format 
	BETWEEN '___' AND '___'

Edit and Run Code