Casting Data

Introduction

Data comes in various types such as integers, strings, dates, and decimals. Sometimes, it becomes necessary to change or "cast" data from one type to another, especially when performing calculations or formatting output. MS SQL provides two primary functions to achieve this: CAST and CONVERT.

Why Casting is Important

Casting ensures that data is in the correct format for computations, comparisons, and display. For example, dates might need to be formatted in a specific way for reports, or numeric values might need to be converted to strings for concatenation in messages.

In a customer management system, common scenarios where casting is useful include:

• Formatting date of birth for display.
• Converting phone numbers stored as integers into strings for consistent formatting.
• Changing customer IDs from numeric to string for concatenation in reference codes.

Syntax

• CAST Syntax:

  SELECT CAST(column_name AS new_data_type)
  FROM table_name;
  

• CONVERT Syntax:

  SELECT CONVERT(new_data_type, column_name, [style]);
  

Tables

Customers

Orders

The style argument in CONVERT is optional and is commonly used with date formatting.

Example 1: Imagine a customer management system where customer birthdays are stored as DATE, but you need to display them in a VARCHAR format for reporting.

SELECT 
    customer_id,
    first_name,
    last_name,
    CAST(birthdate AS VARCHAR(10)) AS formatted_birthdate
FROM 
    Customers;

Here, we cast the birthdate column (stored as a DATE) into a VARCHAR(10) for formatted display.

Example 2: Phone numbers in the database might be stored as integers, but when displaying or exporting them, you might want them formatted with dashes or other characters.

Convert an integer phone number to a string for easy formatting.

SELECT 
    customer_id,
    first_name,
    last_name,
    CONVERT(VARCHAR(15), phone_number) AS formatted_phone
FROM 
    Customers;

Here, we convert the phone_number column from an INT to a VARCHAR(15) so we can apply custom formatting in the presentation layer (like adding dashes).

Example 3: Suppose the customer ID is stored as an integer, but you need to concatenate it with a string to generate a unique customer reference code.

Create a customer reference code by concatenating the string "CUST-" with the customer_id.

SELECT 
    customer_id,
    first_name,
    'CUST-' + CAST(customer_id AS VARCHAR(10)) AS customer_reference
FROM 
    Customers;

In this example, the CAST function converts the integer customer_id into a VARCHAR(10), which allows concatenation with the "CUST-" string.

Example 4: In customer management, pricing information like subscription fees or credit balances may require precise formatting, especially when dealing with decimal values.

Display order amount with two decimal points.

SQL Query:

SELECT 
    customer_id,
    CAST(total_amount AS DECIMAL(10, 2)) AS formatted_credit_balance
FROM 
    Orders;

Here, the credit_balance is cast to DECIMAL(10, 2) to ensure it is displayed with two decimal places.

Example 5: You may need to present dates and times in specific formats for various business reports.

Convert the date_created to a specific format using CONVERT.

SQL Query:

SELECT 
    customer_id,
    first_name,
    CONVERT(VARCHAR, date_created, 101) AS formatted_purchase_date
FROM 
    Customers;

Here, CONVERT is used with style 101 to format the last_purchase_date into MM/DD/YYYY.

Common errors

Some common errors you might encounter when casting data.

• Incompatible data types: For example, trying to cast a string containing alphabetic characters to an integer.
• Data truncation: Casting a large VARCHAR to a smaller size can result in data being truncated.
• Precision loss: Casting a decimal number to an integer results in the loss of any decimal values.

Casting v/s Formatting

Casting data and formatting data are related but serve different purposes in SQL and programming.

1. Casting Data Casting refers to converting a value from one data type to another. This is essential when you need to change the data type to perform specific operations or calculations. Casting affects how the data is interpreted by the system internally.

• Purpose: Ensure that data can be used in calculations or operations by converting it to the appropriate data type.

• Example: Converting an integer to a string so it can be concatenated with other text.

  SELECT CAST(123 AS VARCHAR(10));
  

  • This converts the integer 123 to a string of VARCHAR type.

Changes the data type of the value but not its visual representation unless it's a type like DECIMAL or DATE that implicitly changes how it's stored or calculated.

2. Formatting Data Formatting refers to changing the way data is displayed without altering the underlying data type or value. It is often used for presentation purposes, such as displaying dates in a specific format or showing numbers with a specific number of decimal places. Formatting doesn't change the actual data stored in the database.

• Purpose: Improve the appearance of data for reporting or user interfaces by controlling how it is visually represented.

• Example: Displaying a date in the MM/DD/YYYY format.

  SELECT CONVERT(VARCHAR, GETDATE(), 101) AS formatted_date;
  

  • This formats the current date (GETDATE()) to be displayed as MM/DD/YYYY, but the underlying DATETIME value is still stored in its original format.

Changes how the data appears without modifying its data type or actual stored value.

Key Differences

Conclusion

Casting is a powerful tool for ensuring data consistency and flexibility in SQL queries. In customer management systems, where data types often need to be changed for calculations, formatting, or reporting, casting helps ensure that your queries deliver the correct results. Whether using CAST or CONVERT, the ability to handle data type conversions efficiently is a crucial skill for managing customer data in MS SQL.

Asked in Interview

• What is data casting in SQL, and why is it important?
• What is the difference between CAST and CONVERT in SQL?
• What happens if the cast is not possible? How do you handle such situations?
• What are some common errors you might encounter when casting data?
• How casting data is differnt from formatting data?