CONVERTING DATA TYPES

Key moments

Need for Conversion — Data conversion is necessary when writing SQL statements, such as changing a decimal to an integer.

Introducing CAST Function — The CAST function is used to convert data from its existing type to a new specified type.

CAST to INTEGER — Converting a number like 2.7949 to an integer results in the loss of decimal places and rounding up to 3.

CAST to DECIMAL — You can convert a number to a decimal while specifying the exact number of allowed decimal places.

CAST to VARCHAR — Data can also be converted into a text string using AS VARIABLE CHARACTERS or AS VARCHAR.

Conversion Caveats — Be aware that converting data types, especially to integers, can cause a loss of detail or granularity.

Bottom line: You will learn how to use the CAST function to change data from one type to another, such as converting a decimal number into an integer or a text string. You must be aware that converting types can sometimes result in a loss of detail, requiring careful handling of precision and rounding.

This lesson on CONVERTING DATA TYPES is hands-on and example-driven. You will learn how to use the CAST function to change data from one type to another, such as converting a decimal number into an integer or a text string. You must be aware that converting types can sometimes result in a loss of detail, requiring careful handling of precision and rounding.

What You'll Be Able To Do

Apply the CAST function to change a numeric value to an integer.
Specify the precision when converting a number to a decimal type.
Convert a numeric data type into a variable character string.
Identify scenarios where data granularity is lost during type conversion.
Write SQL statements that utilize CAST for required data transformations.

Topics Covered in CONVERTING DATA TYPES

Need for Conversion (0:00 - 0:05) — Data conversion is necessary when writing SQL statements, such as changing a decimal to an integer.
Introducing CAST Function (0:05 - 0:15) — The CAST function is used to convert data from its existing type to a new specified type.
CAST to INTEGER (0:15 - 0:30) — Converting a number like 2.7949 to an integer results in the loss of decimal places and rounding up to 3.
CAST to DECIMAL (0:30 - 0:45) — You can convert a number to a decimal while specifying the exact number of allowed decimal places.
CAST to VARCHAR (0:45 - 0:55) — Data can also be converted into a text string using AS VARIABLE CHARACTERS or AS VARCHAR.
Conversion Caveats (0:55 - 1:10) — Be aware that converting data types, especially to integers, can cause a loss of detail or granularity.

SQL Cheat Sheet

CAST(<expression> AS <type>) — Converts data from its current type to the specified new type
```
SELECT CAST(2.7949 AS INTEGER);
```
AS INTEGER — Converts a number to a whole number, losing decimal precision
```
SELECT CAST(2.7949 AS INTEGER); -- Result: 3
```
AS DECIMAL(P, S) — Converts a number, specifying total digits (P) and scale (S)
```
SELECT CAST(2.7949 AS DECIMAL(3, 2)); -- Result: 2.79
```
AS VARIABLE CHARACTERS — Converts a numeric value into a text string data type
```
SELECT CAST(2.7949 AS VARCHAR);
```

Comparison Table

Resulting Data Type	Conversion Goal	Output for 2.7949
INTEGER	Whole number counting	3 (Rounded up)
DECIMAL(X, 2)	Fixed precision decimal	2.79 (Shortened)
VARIABLE CHARACTERS	Text string representation	'2.7949' (Text)

Common Pitfalls

Mistake: Assuming decimal values are truncated when converted to integers. Avoid: Remember that CAST often rounds the value to the nearest whole number.
Mistake: Converting to a decimal type without specifying required precision. Avoid: Always define the precision and scale (P, S) to control the output format.
Mistake: Expecting the original data type to remain after conversion. Avoid: The new data type dictates how the value is stored and displayed.

FAQs

Why do I need to convert data types? You need conversions when data types are incompatible for an operation, or when you require a specific format, like rounding to a whole number.
Does CAST change the data stored in the database? No, CAST only changes the data type for the duration of the query execution. The underlying stored data remains unchanged.
What happens when I convert a decimal to an integer? The decimal portion is lost, and the number is typically rounded up or down to the nearest whole integer.

So occasionally when we're writing SQL statements, we're going to want to convert our data from one type to another. For example, maybe we want to change a decimal to an integer. So let me show you how to do that. So, we can use cast, that function, to convert data from one type to another. So let's say we have a number and in this case, one row of the number is 2.7949. We could take that number and convert it as an integer. So cast this number as an integer. Cast is this conversion process. Now, as we do that conversion, we're going to lose, in this case, a little bit of granularity because an integer is a whole number, a counted number, and so you're going to lose the decimal and it's kind of just rounding up to the the next integer there. So we're taking this number, converting it as an integer. Now, I'm changing this a little bit now, so I'm casting this number, or I'm converting it from its existing type to a decimal. Now, it's listed or showing as a decimal right now, but what I'm saying is that only allow two spaces, that's the second digit there, after the decimal. So this will convert this to 2.79. It's going to shorten it because that's all that, I've done this conversion, that's all that new type is going to allow. We can also take the number in this case and cast it or convert it as variable characters or a text string. And so we're just going to get a text string back in this case. So we can take different data types and convert them. Just know that in some cases we lose a little bit of detail, like when we convert this number to an integer, and it rounds it up to three. Just something to be aware of as we handle these conversions, but we can take data and convert it from one type to another as we write our SQL statements, using that cast function. Thanks for watching. If you like this video, click the subscribe button. And if you're interested in the SQL cheat sheet, you can find one over at codybaldwin.com.

Frequently asked questions

Why do I need to convert data types?

You need conversions when data types are incompatible for an operation, or when you require a specific format, like rounding to a whole number.

Does CAST change the data stored in the database?

No, CAST only changes the data type for the duration of the query execution. The underlying stored data remains unchanged.

What happens when I convert a decimal to an integer?

The decimal portion is lost, and the number is typically rounded up or down to the nearest whole integer.

Ready to continue?