CAST and CONVERT for type changes in SQL - Mini Project: Build & Apply

Using CAST and CONVERT for Type Changes in SQL

📖 Scenario: You are working with a sales database that stores product prices as text strings. To perform calculations, you need to convert these text prices into numbers.

🎯 Goal: Build a SQL query that converts text prices to numeric values using CAST and CONVERT functions.

📋 What You'll Learn

Create a table called products with columns product_id (integer) and price_text (varchar).

Insert three rows with product IDs 1, 2, 3 and prices '10.50', '20.75', '15.00' as text.

Write a query that uses CAST to convert price_text to a decimal number.

Write a query that uses CONVERT to convert price_text to a decimal number.

💡 Why This Matters

🌍 Real World

Converting data types is common when data is stored as text but needs to be used in calculations or comparisons.

💼 Career

Database developers and analysts often use CAST and CONVERT to ensure data is in the correct format for queries and reports.

1

Create the products table and insert data

Create a table called products with columns product_id as INTEGER and price_text as VARCHAR(10). Insert three rows with product_id values 1, 2, 3 and price_text values '10.50', '20.75', and '15.00'.

SQL

-- Create the products table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Add a query using CAST to convert price_text to decimal

Write a SQL query that selects product_id and converts price_text to a decimal number using CAST(price_text AS DECIMAL(5,2)). Name the converted column price_cast.

SQL

CREATE TABLE products (
    product_id INTEGER,
    price_text VARCHAR(10)
);

INSERT INTO products (product_id, price_text) VALUES
(1, '10.50'),
(2, '20.75'),
(3, '15.00');

-- Write a SELECT query using CAST to convert price_text
-- Your code here

Hint

Use CAST(column AS type) in the SELECT statement.

3

Add a query using CONVERT to convert price_text to decimal

Write a SQL query that selects product_id and converts price_text to a decimal number using CONVERT(DECIMAL(5,2), price_text). Name the converted column price_convert.

SQL

CREATE TABLE products (
    product_id INTEGER,
    price_text VARCHAR(10)
);

INSERT INTO products (product_id, price_text) VALUES
(1, '10.50'),
(2, '20.75'),
(3, '15.00');

SELECT product_id, CAST(price_text AS DECIMAL(5,2)) AS price_cast
FROM products;

-- Write a SELECT query using CONVERT to convert price_text
-- Your code here

Hint

Use CONVERT(type, column) in the SELECT statement.

4

Complete the queries with ORDER BY clause

Add an ORDER BY product_id clause to both SELECT queries that use CAST and CONVERT to sort the results by product_id.

SQL

CREATE TABLE products (
    product_id INTEGER,
    price_text VARCHAR(10)
);

INSERT INTO products (product_id, price_text) VALUES
(1, '10.50'),
(2, '20.75'),
(3, '15.00');

SELECT product_id, CAST(price_text AS DECIMAL(5,2)) AS price_cast
FROM products
-- Add ORDER BY clause here

SELECT product_id, CONVERT(DECIMAL(5,2), price_text) AS price_convert
FROM products
-- Add ORDER BY clause here

Hint

Use ORDER BY product_id at the end of each SELECT query.

Practice

1. What is the main purpose of using CAST or CONVERT in SQL?

easy

A. To change the data type of a value

B. To delete rows from a table

C. To create a new table

D. To join two tables

Solution

1. Step 1: Understand the function of CAST and CONVERT

   Both CAST and CONVERT are used to change the data type of a value in SQL.

2. Step 2: Compare with other options

   Deleting rows, creating tables, and joining tables are unrelated to changing data types.

3. Final Answer:

   To change the data type of a value -> Option A

4. Quick Check:

   CAST and CONVERT change types = A [OK]

Hint: CAST and CONVERT always change data types [OK]

Common Mistakes:

• Thinking CAST deletes data
• Confusing CONVERT with table creation
• Mixing data type change with joins

2. Which of the following is the correct syntax to convert a string '123' to an integer using CAST in SQL?

easy

A. CAST('123' AS INT)

B. CAST(INT AS '123')

C. CONVERT('123', INT)

D. CONVERT(INT, '123')

Solution

1. Step 1: Recall CAST syntax

   CAST requires the format CAST(value AS datatype), so CAST('123' AS INT) is correct.

2. Step 2: Check other options

   Options A, B, and D use incorrect order or syntax for CAST or CONVERT.

3. Final Answer:

   CAST('123' AS INT) -> Option A

4. Quick Check:

   CAST(value AS type) = C [OK]

Hint: CAST syntax is always CAST(value AS datatype) [OK]

Common Mistakes:

• Swapping value and datatype order
• Using CONVERT with wrong argument order
• Missing AS keyword in CAST

3. Given the SQL query:

SELECT CAST('2024-06-15' AS DATE) AS ConvertedDate;

What will be the output type of ConvertedDate?

medium

A. String

B. Boolean

C. Integer

D. Date

Solution

1. Step 1: Analyze the CAST operation

   The query casts the string '2024-06-15' to the DATE data type.

2. Step 2: Determine the output type

   Since CAST converts to DATE, the output column ConvertedDate will be of type Date.

3. Final Answer:

   Date -> Option D

4. Quick Check:

   CAST to DATE returns Date type = D [OK]

Hint: CAST to DATE returns a date type value [OK]

Common Mistakes:

• Assuming output stays string
• Confusing DATE with integer
• Thinking CAST changes value format only

4. Identify the error in this SQL statement:

SELECT CONVERT(INT, 'abc123');

medium

A. No error, converts string to integer

B. Syntax error due to wrong CONVERT argument order

C. Runtime error converting non-numeric string to INT

D. Missing AS keyword in CONVERT

Solution

1. Step 1: Check CONVERT syntax

   CONVERT(INT, 'abc123') is correct syntax in some SQL dialects like SQL Server.

2. Step 2: Consider conversion feasibility

   Trying to convert 'abc123' to INT causes a runtime error because 'abc123' is not numeric.

3. Final Answer:

   Runtime error converting non-numeric string to INT -> Option C

4. Quick Check:

   Non-numeric string to INT causes runtime error = A [OK]

Hint: Non-numeric strings cause errors when converting to INT [OK]

Common Mistakes:

• Assuming CONVERT always succeeds
• Confusing syntax error with runtime error
• Expecting implicit string to int conversion

5. You have a table Orders with a column OrderDate stored as VARCHAR in format 'YYYYMMDD'.
Which SQL query correctly converts OrderDate to a DATE type and selects orders from June 2024?

Options:
A) SELECT * FROM Orders WHERE CAST(OrderDate DATE) BETWEEN '2024-06-01' AND '2024-06-30';
B) SELECT * FROM Orders WHERE CONVERT(DATE, OrderDate, 112) BETWEEN '2024-06-01' AND '2024-06-30';
C) SELECT * FROM Orders WHERE CAST(OrderDate AS INT) BETWEEN 20240601 AND 20240630;
D) SELECT * FROM Orders WHERE CONVERT(INT, OrderDate) BETWEEN 20240601 AND 20240630;

hard

A. Option A only

B. Option B only

C. Option C only

D. Option D only

Solution

1. Step 1: Understand the data format and conversion

   OrderDate is VARCHAR in 'YYYYMMDD' format. CONVERT with style 112 converts this string correctly to DATE.

2. Step 2: Evaluate each option

   Option B only only tries CAST without style, which may fail or misinterpret format. Option B only only uses CONVERT with style 112, correct for 'YYYYMMDD'. Options C and D convert to INT, which is not reliable for date comparisons.

3. Final Answer:

   Option B only -> Option B

4. Quick Check:

   CONVERT with style 112 converts 'YYYYMMDD' to DATE = B [OK]

Hint: Use CONVERT with style 112 for 'YYYYMMDD' to DATE [OK]

Common Mistakes:

• Using CAST without format for 'YYYYMMDD'
• Converting dates to INT for comparison
• Ignoring CONVERT style parameter