\nCAST(expression AS target_data_type)\n<\/code><\/pre>\nAnd here\u2019s a quick example:<\/p>\n
\nSELECT CAST('123' AS INT) AS converted_number;\n<\/code><\/pre>\nThis takes the string ‘123’ and converts it into an integer, so you can do math with it.<\/p>\n
Why Would You Use CAST?<\/h2>\n
Good question! Here are some common reasons:<\/p>\n
\n- Combine Different Data Types<\/b>: For example, adding a number to a string version of a number.<\/li>\n
- Format Output<\/b>: Like turning a float into a string for neat reports.<\/li>\n
- Fix Errors<\/b>: Prevent type mismatch errors in joins or comparisons.<\/li>\n<\/ul>\n
Let\u2019s see it in real life.<\/p>\n
\nSELECT 'The price is ' + CAST(price AS VARCHAR)\nFROM products;\n<\/code><\/pre>\nIn this case, the number (price) is converted to text so it can be joined with other text.<\/p>\n![\"\"](\"https:\/\/resizemyimg.com\/blog\/wp-content\/uploads\/2025\/12\/computer-screen-showing-lines-of-code-sql-query-cast-function-convert-data-types.jpg\")
\n
CAST vs CONVERT<\/h2>\n
SQL gives you two<\/i> functions to change data types: CAST<\/b> and CONVERT<\/b>.<\/p>\n
Both do similar things, but here\u2019s how they differ:<\/p>\n
\n- CAST<\/b> is a standard SQL function. It works the same across different database systems.<\/li>\n
- CONVERT<\/b> is Microsoft SQL Server-specific and gives you more options for formatting dates and numbers.<\/li>\n<\/ul>\n
Here\u2019s an example of CONVERT:<\/p>\n
\nSELECT CONVERT(VARCHAR, GETDATE(), 101) AS formatted_date;\n<\/code><\/pre>\nThis converts the current date into a MM\/DD\/YYYY format. Pretty cool, right?<\/p>\n
Common Data Type Conversions<\/h2>\n
Here are the usual suspects when it comes to conversion:<\/p>\n
\n- String to Integer:<\/b> CAST(‘789’ AS INT)<\/li>\n
- Integer to String:<\/b> CAST(789 AS VARCHAR)<\/li>\n
- Date to String:<\/b> CAST(GETDATE() AS VARCHAR)<\/li>\n
- Float to Integer:<\/b> CAST(123.45 AS INT)<\/li>\n<\/ul>\n
Be aware: when you convert from float to int, you lose the decimals!<\/p>\n
\nSELECT CAST(123.99 AS INT) AS result;\n-- Output: 123 (not 124)\n<\/code><\/pre>\nSQL just chops off the decimal. It doesn\u2019t round it. So be careful!<\/p>\n
Using CAST in Math<\/h2>\n
Imagine you’re doing division and both numbers are integers. SQL will return an integer result\u2014no decimals!<\/p>\n
\nSELECT 10 \/ 4 AS result;\n-- Output: 2\n<\/code><\/pre>\nOops! Where\u2019s the 0.5? You\u2019ve lost it because integers were used.<\/p>\n
Let\u2019s fix it with CAST:<\/p>\n
\nSELECT CAST(10 AS FLOAT) \/ 4 AS result;\n-- Output: 2.5\n<\/code><\/pre>\nMuch better.<\/p>\n
CAST in WHERE Clauses<\/h2>\n
You can also use CAST in WHERE<\/b> clauses to help filter data.<\/p>\n\nSELECT *\nFROM orders\nWHERE CAST(order_date AS DATE) = '2024-01-01';\n<\/code><\/pre>\nThis helps if order_date<\/code> is actually a datetime and you only care about the date part.<\/p>\nCombining CAST with filters = powerful stuff!<\/p>\n
Nested CASTs<\/h2>\n
Can you convert something, then convert it again? Absolutely.<\/p>\n
\nSELECT CAST(CAST(price AS FLOAT) AS VARCHAR)\nFROM products;\n<\/code><\/pre>\nThis takes a number, turns it into a float, and then into text. It\u2019s a \u201cconversion sandwich\u201d!<\/p>\n
When Not to Use CAST<\/h2>\n
Sometimes, casting is not the best idea. Be cautious when:<\/p>\n
\n- You Could Lose Data<\/b>: Converting float to int removes decimals.<\/li>\n
- The Format Doesn\u2019t Match<\/b>: Converting ‘abc’ to INT will cause an error.<\/li>\n
- Your Database Already Stores Correct Types<\/b>: Don\u2019t CAST just because!<\/li>\n<\/ul>\n
Always test your CAST before using it in huge queries.<\/p>\n![\"\"](\"https:\/\/resizemyimg.com\/blog\/wp-content\/uploads\/2025\/12\/a-black-and-white-photo-of-a-person-on-a-skateboard-sql-error-failed-cast-type-mismatch.jpg\")
\n
Bonus: Using TRY_CAST<\/h2>\n
Some databases (like SQL Server) have TRY_CAST<\/b>. It’s like CAST, but safer.<\/p>\n
Instead of throwing an error, it returns NULL when it fails.<\/p>\n
\nSELECT TRY_CAST('oops' AS INT) AS result;\n-- Output: NULL\n<\/code><\/pre>\nThis is great for cleaning messy data or avoiding query crashes.<\/p>\n
Real World Example<\/h2>\n
Let\u2019s say you have a table of survey responses, and a column is stored as text\u2014like \u201cage\u201d values.<\/p>\n
To calculate the average age, you\u2019ll need to turn those text values into numbers:<\/p>\n
\nSELECT AVG(CAST(age AS INT)) AS average_age\nFROM survey;\n<\/code><\/pre>\nNow you\u2019ve got useful data from a weird column. That\u2019s the power of CAST!<\/p>\n
Recap<\/h2>\n
CAST is your best friend when dealing with mismatched data types in SQL. It helps you:<\/p>\n