CAST and CONVERT for type changes in SQL - Step-by-Step Execution

Concept Flow - CAST and CONVERT for type changes

Start with original value

↓

Choose target data type

↓

Apply CAST or CONVERT

↓

Check if conversion is valid

↓

Return converted value

↓

End

The flow shows how a value is taken, a target type is chosen, then CAST or CONVERT changes the type, checking validity before returning the result or error.

Execution Sample

SQL

SELECT CAST('123' AS INT) AS cast_result,
       CONVERT(VARCHAR, 456) AS convert_result;

This query converts a string '123' to an integer using CAST and converts an integer 456 to a string using CONVERT.

Execution Table

Step	Expression	Operation	Result	Notes
1	CAST('123' AS INT)	Convert string '123' to INT	123	String '123' is numeric, conversion succeeds
2	CONVERT(VARCHAR, 456)	Convert INT 456 to string	'456'	Integer 456 converted to string '456'
3	CAST('abc' AS INT)	Attempt to convert non-numeric string	Error or NULL	Conversion fails, returns error or NULL depending on SQL dialect
4	CONVERT(DATE, '2024-06-01')	Convert string to DATE	2024-06-01	Valid date string converted to DATE type
5	CAST(123.45 AS INT)	Convert float to INT	123	Decimal truncated to integer part
6	End	No more expressions	Query returns two columns with results	Execution complete

💡 All expressions processed; conversion errors handled by returning NULL or error depending on SQL dialect

Variable Tracker

Expression	Initial Value	After Conversion
CAST('123' AS INT)	'123' (string)	123 (int)
CONVERT(VARCHAR, 456)	456 (int)	'456' (string)
CAST('abc' AS INT)	'abc' (string)	Error or NULL
CONVERT(DATE, '2024-06-01')	'2024-06-01' (string)	2024-06-01 (date)
CAST(123.45 AS INT)	123.45 (float)	123 (int)

Key Moments - 3 Insights

Why does CAST('abc' AS INT) fail while CAST('123' AS INT) works?

Because 'abc' is not a numeric string, the conversion to INT is invalid and causes an error or NULL, as shown in execution_table step 3.

What happens when converting a float to an INT using CAST?

The decimal part is truncated, not rounded, so 123.45 becomes 123, as shown in execution_table step 5.

How does CONVERT differ from CAST in usage?

CONVERT allows specifying the target type first and can include style parameters for formatting; CAST uses a simpler syntax. Both perform type changes as shown in steps 1 and 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of CAST('123' AS INT) at step 1?

A '123'

B 123

C Error

D NULL

💡 Hint

Check the 'Result' column in execution_table row for step 1

At which step does the conversion fail due to invalid input?

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

Look for 'Error or NULL' in the 'Result' column in execution_table

If we change CAST(123.45 AS INT) to CAST(123.99 AS INT), what would be the result?

A 123

B Error

C 124

D NULL

💡 Hint

Refer to execution_table step 5 showing truncation, not rounding

Concept Snapshot

CAST and CONVERT change data types in SQL.
CAST(expression AS target_type) is standard syntax.
CONVERT(target_type, expression) is similar, with optional style.
Invalid conversions cause errors or NULL.
Casting float to int truncates decimals.
Useful for data type compatibility and formatting.

Full Transcript

This lesson shows how SQL changes data types using CAST and CONVERT. We start with a value, choose a target type, then apply CAST or CONVERT. If the conversion is valid, the new value is returned; if not, an error or NULL occurs. For example, casting the string '123' to integer gives 123, but casting 'abc' to integer fails. Converting integer 456 to string results in '456'. Casting a float like 123.45 to integer truncates to 123. These functions help manage data types in queries.

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