Data Type Conversion in SQL
Introduction
Data type conversion is the process of changing a value from one SQL data type to another. A text value such as '100' may become an INTEGER for arithmetic, an INTEGER may become DECIMAL for fractional calculation, and a timestamp may become DATE when only its calendar portion is required.
Conversion is essential when databases compare unlike types, import text files, clean legacy records, combine sources, accept application parameters, format reports, or migrate schemas. It is also risky. A conversion can fail, overflow, round, truncate, change time semantics, remove leading zeros, prevent index use, or silently alter a result.
What Data Type Conversion Means
A conversion interprets a source value under the rules of a target type. The source representation, target range, precision, scale, format, collation, and database product determine whether the operation succeeds and whether it preserves information.
SELECT CAST('100' AS INTEGER) AS quantity;
SELECT CAST(100 AS DECIMAL(10, 2)) AS amount;
The first expression changes text into a whole number. The second changes an integer into an exact decimal and returns a value conceptually represented as 100.00. CAST changes the value's SQL type; it does not change what the underlying business fact means.
Why Conversion Is Needed
External systems frequently deliver values in a representation different from the one needed for storage or calculation. CSV fields are text, JSON can contain strings and numbers, legacy tables may hold dates in character columns, and application frameworks may bind parameters with unsuitable types.
Conversion is appropriate at a well-defined boundary. Import text can be validated and converted into typed production columns. An integer can be promoted before division. A typed date can be converted to text for export. Conversion should not become a permanent substitute for a correct schema.
Main Conversion Categories
| Source | Target | Typical purpose | Main risk |
|---|---|---|---|
| Text | Numeric | Imports and calculation | Invalid syntax or overflow |
| Numeric | Text | Display and export | Formatting or length loss |
| Text | Date or timestamp | Legacy and API input | Ambiguous formats |
| Decimal | Integer | Whole-number requirement | Rounding or truncation |
| Exact numeric | Approximate numeric | Scientific processing | Precision loss |
| Binary | Text | Transport encoding | Encoding confusion |
| Text | JSON | Validated documents | Invalid JSON structure |
| Timestamp | Date | Calendar grouping | Loss of time and zone |
Explicit and Implicit Conversion
Explicit conversion occurs when SQL states the target type directly. It exposes intent in code and gives the developer control over type, precision, and scale. Implicit conversion occurs when the database chooses a common type automatically during comparison, arithmetic, assignment, CASE, UNION, or function evaluation.
| Feature | Explicit conversion | Implicit conversion |
|---|---|---|
| Who selects the target? | Developer | Database engine |
| Visibility | Clear in SQL | Often hidden |
| Portability | More predictable with CAST | Precedence rules vary |
| Performance review | Easier to locate | May appear only in plans |
| Recommended use | At intentional boundaries | Only when behavior is proven |
CAST Syntax
CAST is the SQL-standard conversion expression and should be the default when portable SQL is required.
CAST(expression AS target_data_type)
SELECT CAST('123' AS INTEGER);
SELECT CAST(order_total AS DECIMAL(14, 2));
SELECT CAST(created_at AS DATE);
The exact available target names and error behavior depend on the database. The syntax is portable, but a successful conversion on one platform does not guarantee identical range, date parsing, or rounding on another.
Vendor-Specific Conversion Functions
SQL Server provides CONVERT for conversion plus style codes, and TRY_CAST or TRY_CONVERT for failure-tolerant parsing. PostgreSQL supports CAST and the shorthand expression::type. Oracle commonly uses TO_NUMBER, TO_DATE, and TO_CHAR with explicit format models. MySQL and SQLite support CAST but differ in coercion and invalid-input behavior.
Use database-specific functions when they provide a necessary capability, such as a controlled date format or safe parsing. Isolate and document them when portability matters. Standard CAST remains the clearest baseline.
String to Integer Conversion
A valid integer string can usually be converted directly.
SELECT CAST('250' AS INTEGER);
Signs, whitespace, grouping separators, decimal points, currency symbols, exponent notation, and locale rules may affect validity. Do not assume that a value accepted by one engine is accepted by another. Validate the complete string rather than extracting any digits that happen to appear.
Invalid Numeric Text
Values such as '12A', '$45.00', or an empty string are not universally valid numeric input. A strict conversion may raise an error; a permissive engine may return zero, a partial value, or NULL. Silent coercion can be more dangerous than failure because incorrect data enters calculations unnoticed.
Preserve the raw value in an import table, record the rejection reason, and convert only rows that meet an explicit format and range policy. Do not use COALESCE to turn a failed parse into zero unless zero is the defined business result.
Integer to String Conversion
Numeric-to-character conversion is useful for exports, labels, or concatenation, but the target length must be sufficient for the sign and every digit. Formatting such as grouping separators, currency symbols, padding, and localized decimal marks is different from conversion and usually belongs in a presentation layer.
SELECT CAST(order_id AS VARCHAR(30)) AS order_id_text
FROM orders;
Keeping identifiers numeric in storage and converting only for an external text boundary preserves numeric comparison and index behavior.
String to Decimal Conversion
Specify precision and scale deliberately when converting financial or measured text.
SELECT CAST('12345.67' AS DECIMAL(12, 2));
The target must accommodate digits on both sides of the decimal point. Values with extra fractional digits may be rounded, rejected, or handled according to product settings. Currency separators and locale-specific decimal marks should be normalized under an explicit import contract.
Integer and Decimal Conversion
Integer-to-decimal conversion is generally widening and preserves the whole value when the target has sufficient precision. Decimal-to-integer conversion is narrowing because the fractional component cannot survive.
SELECT CAST(42 AS DECIMAL(10, 2));
SELECT CAST(42.75 AS INTEGER);
The second result can differ by product or settings because some databases round and others truncate in particular contexts. If the business rule requires rounding, state it before the cast.
SELECT CAST(ROUND(amount, 0) AS INTEGER)
FROM payments;
Truncation Versus Rounding
Truncation removes digits without considering their value, while rounding changes the retained digit according to a policy. Neither is automatically correct. Tax calculations, inventory units, scientific measurements, and user-facing reports may require different rules.
Define midpoint behavior, negative-number behavior, and the stage at which rounding occurs. Test values immediately below, at, and above boundaries. Avoid relying on an undocumented side effect of CAST.
Widening and Narrowing Conversion
A widening conversion moves to a type that can represent all source values, such as INTEGER to BIGINT under normal ranges. A narrowing conversion moves to a smaller range, shorter length, lower precision, or reduced temporal detail.
Widening is usually safer but can still change application mappings or index width. Narrowing requires profiling before execution. Check minimum, maximum, length, precision, scale, fractional content, Unicode behavior, and NULLs rather than waiting for one invalid row to fail a migration.
Overflow and Range Checking
BIGINT to INTEGER can fail when a source value is outside the target range. Similar risk exists when converting large decimals, timestamps outside a product's supported range, or long text into shorter columns.
SELECT MIN(source_id), MAX(source_id)
FROM legacy_events;
Range checks must reflect the actual target product and signedness. Run them against all partitions and archived data. A current maximum below the limit is not enough if growth can exhaust the type soon after migration.
Precision and Scale Loss
Converting DECIMAL(18, 6) to DECIMAL(10, 2) can lose fractional detail and may overflow because fewer integer digits remain. Precision is the total digit count, not only the digits before the decimal point.
Profile the largest integer portion and maximum observed fractional scale. Recalculate critical aggregates before and after conversion. For financial migrations, compare counts, sums, minimums, maximums, and values at rounding boundaries.
Exact and Approximate Numerics
DECIMAL-to-FLOAT conversion can introduce binary approximation even when the printed value looks unchanged. FLOAT-to-DECIMAL can expose or round approximation already present. Neither direction is automatically lossless.
Do not convert money to FLOAT for convenience. Use approximate types for domains that tolerate approximation, and define tolerance-based comparison. Use exact decimal types when digits must be preserved contractually.
NULL Conversion
Casting NULL normally returns NULL of the target type. It does not produce zero, empty text, false, or a default date.
SELECT CAST(NULL AS INTEGER);
COALESCE changes missing-value behavior and should be used only when a fallback has business meaning. It is not a conversion error handler by itself, and replacing unknown amounts with zero can alter totals and averages.
String to Date Conversion
Date parsing is safe only when input has an unambiguous contract. ISO-style year-month-day values are usually clearer than formats where day and month positions depend on locale.
SELECT CAST('2026-10-31' AS DATE);
A value such as '03/04/2026' can mean March 4 or April 3. Use an explicit format model where supported, reject impossible dates, and avoid dependence on session language or regional settings.
Date to String Conversion
Converting a date to character data creates text. Formatting controls how that text appears. These concerns overlap in vendor functions but should remain conceptually separate.
Use a stable machine format for data interchange and localized formatting at the user-interface boundary. Once a date becomes formatted text, date arithmetic and chronological filtering should continue to use the original typed column.
Timestamp Conversions
Timestamp-to-date conversion removes the time component. DATE-to-timestamp conversion introduces a time, commonly midnight, that did not exist in the source. Both can be valid when those semantics are intentional.
Time-zone conversion is more than casting. A cast may drop an offset, reinterpret a local value, or simply change representation according to the product. Distinguish an absolute instant from a local wall-clock value and use explicit zone functions when the instant must move between zones.
Boolean Conversion
Databases disagree about Boolean types and accepted text or numeric forms. Some accept TRUE and FALSE, some use BIT, and some treat nonzero numbers as true. For portable mappings, CASE makes the accepted vocabulary explicit.
CASE UPPER(status_text)
WHEN 'YES' THEN 1
WHEN 'NO' THEN 0
ELSE NULL
END
Keep invalid input separate from a legitimate unknown state. A failed parse should not silently become false.
Binary and Text Conversion
Binary bytes do not automatically correspond to readable characters. Converting between bytes and text requires an encoding such as UTF-8 for character content, or an external representation such as hexadecimal or Base64 for arbitrary bytes.
Encoding is not the same as CAST. Hashes, signatures, encrypted values, and files should retain byte semantics in storage. Record the encoding when text must cross a text-only boundary, and verify a byte-for-byte round trip.
JSON Conversion
Text-to-JSON conversion should validate that the complete document follows JSON syntax. Converting a JSON scalar also requires attention to whether the value is a JSON number, string, Boolean, or null.
The JSON values 100 and "100" are different types. Extracting either as SQL INTEGER may produce the same number, but accepting both can hide an API contract defect. Decide whether coercion is allowed and validate schema before conversion.
Type Precedence
When unlike types appear in one expression, a database applies type-precedence rules to choose a common type. This affects comparisons, arithmetic, CASE, UNION, concatenation, function arguments, and assignments.
If a high-precedence numeric column is compared with a text parameter, the engine may try to convert the text to numeric. One malformed value can fail the statement. Because precedence differs by database, make important conversions explicit and inspect actual result types.
Conversion in Arithmetic
Expression types can change results before an outer CAST occurs. Integer division may discard the fractional part, so casting the final result is too late.
-- Promote an operand before division
SELECT CAST(completed_count AS DECIMAL(12, 4))
/ NULLIF(total_count, 0) * 100 AS completion_percent
FROM course_progress;
Review the types of intermediate expressions, not only the selected alias. SUM and AVG can also return product-specific promoted types; cast at the point where precision is required and confirm overflow limits.
Conversion in CASE and UNION
CASE branches need a compatible result type. If one branch returns INTEGER and another text, precedence may force an unexpected conversion and fail on rows that reach the incompatible branch. Return consistently typed values.
UNION aligns columns by position and selects a common type. Using conversion to combine semantically different fields creates a misleading result. Convert only when columns represent the same domain in different physical forms, and normalize them before the UNION when possible.
Conversion in INSERT and UPDATE
Assignment conversion happens when an inserted or updated expression does not match the destination column. Relying on it can hide truncation, rounding, or invalid application parameters. Bind the intended type and validate before assignment.
Some engines reject over-length text, while configurations in others may truncate or warn. Treat warnings as data-quality events. A successful statement is not proof that every source value survived unchanged.
Conversion in WHERE Clauses
Applying a function or CAST to an indexed column can prevent an ordinary index seek because the engine must calculate a value for many rows.
-- Avoid when order_id is an indexed integer
WHERE CAST(order_id AS VARCHAR(30)) = '10025'
-- Prefer a typed parameter
WHERE order_id = 10025
Convert the external parameter once, then compare it with the column in the column's native type. This supports sargability and gives invalid input a clear boundary.
Date Filtering without Column Conversion
Converting a timestamp column to DATE in a predicate can also suppress efficient index use. A half-open range usually expresses the same requirement without transforming every stored value.
WHERE created_at >= TIMESTAMP '2026-10-31 00:00:00'
AND created_at < TIMESTAMP '2026-11-01 00:00:00'
Half-open ranges avoid end-of-day precision assumptions and work across timestamp resolutions when literals are adapted to the database.
Conversion in JOIN Conditions
Joining INTEGER to VARCHAR forces conversion, may reject dirty values, and can prevent indexes on one side from being used efficiently. It also signals that related columns do not share one physical domain.
Correct the schema so primary and foreign key columns match. During a staged migration, convert and validate once into a clean column rather than casting inside every join.
Sorting, Grouping, and Aggregation
Text numbers sort lexically, placing 100 before 20. Casting in ORDER BY repairs the display query but requires work and can reveal invalid rows at runtime. Store quantities as numbers.
Conversion in GROUP BY can merge previously distinct representations, such as '01' and '1', or split values through formatting. Aggregation can overflow before a final cast, so promote inputs when the intermediate range demands it.
Safe Parsing Functions
Failure-tolerant functions such as SQL Server TRY_CAST return NULL instead of aborting for many invalid values.
SELECT TRY_CAST(raw_amount AS DECIMAL(12, 2)) AS parsed_amount
FROM import_stage;
This is useful for profiling and staging, but NULL can hide whether the source was missing or malformed. Store a validation status or rejection reason and count failures explicitly.
Import and Staging Tables
Imports should separate capture from validation. A staging table preserves raw fields and source metadata. Validation queries classify each row, and only accepted values move into typed production columns.
CREATE TABLE order_import_stage (
source_row_number INTEGER NOT NULL,
raw_order_id VARCHAR(100),
raw_amount VARCHAR(100),
raw_ordered_at VARCHAR(100),
validation_status VARCHAR(20),
rejection_reason VARCHAR(500)
);
This design makes bad data observable, supports reprocessing after rules change, and avoids losing original evidence through destructive early conversion.
ETL Conversion Workflow
- Capture the source without altering its representation.
- Normalize permitted whitespace, encoding, and format differences.
- Validate syntax, range, length, precision, and business rules.
- Convert explicitly into the intended target type.
- Route rejected rows with actionable reasons.
- Reconcile source, accepted, rejected, and loaded counts.
- Verify aggregates and representative round trips.
Application Parameters and APIs
Parameterized queries reduce conversion problems when applications bind values with the same type as the column. They also separate data from SQL syntax and are essential for injection prevention. Conversion and validation do not replace parameterization.
API contracts should distinguish JSON numbers, strings, Boolean values, and null. Large database integers can exceed JavaScript's exact numeric range and may need a documented string representation at the API boundary. DECIMAL values may also travel as strings to preserve exact digits.
ORM and Driver Mappings
ORMs map database types to language types, but defaults may not match the domain. DECIMAL should map to an arbitrary-precision decimal class rather than binary floating point. DATE should not accidentally acquire a time zone, and timestamps need a clearly selected instant or local type.
JDBC and other drivers provide typed setters and getters. Use them instead of converting everything through strings. Test NULL handling, unsigned ranges, time zones, precision, and large values across the actual driver version.
Conversion in Views and Generated Columns
A view can expose a clean type over legacy storage, and a generated column can materialize a converted value for indexing where supported. These are useful transition tools but do not repair the underlying dirty data.
Document failure behavior and dependency order. If malformed source data can break the view, consumers remain exposed. A durable solution validates and migrates the stored domain.
Schema Type Conversion
Changing a column type is a data migration. The operation can rewrite a table, rebuild indexes, lock metadata, grow transaction logs, delay replication, invalidate views, and require synchronized application deployment.
Before conversion, inventory dependencies, profile every value, define loss policy, rehearse on production-scale copies, estimate runtime and storage, and prepare rollback. Converting a column successfully does not prove that its business meaning was preserved.
Safe Schema Migration Pattern
- Add a new column with the correct target type.
- Backfill validated values in bounded batches.
- Record and resolve rows that fail conversion.
- Compare counts, ranges, checksums, and aggregates.
- Dual-write temporarily when zero downtime requires it.
- Switch readers after compatibility testing.
- Add final constraints and indexes.
- Retire the old column only after the recovery window.
Data Loss Categories
| Conversion | Possible loss | Example control |
|---|---|---|
| Text to numeric | Leading zeros or invalid characters | Classify identifiers first |
| Decimal to integer | Fractional component | Explicit rounding policy |
| Timestamp to date | Time and offset | Retain source or prove irrelevance |
| DECIMAL to FLOAT | Exact decimal digits | Use exact target when required |
| Unicode to limited text | Characters or normalization | Compatible character set |
| Long text to short text | Trailing content | Length profiling and rejection |
| Binary to text | Byte identity | Explicit reversible encoding |
Round-Trip Validation
A conversion is round-trip safe when source to target to source returns the same value under the required equivalence rule. INTEGER to BIGINT to INTEGER is safe while values remain in range. DECIMAL to FLOAT to DECIMAL is not guaranteed to reproduce the original exact digits.
Round-trip tests should include boundaries, negative values, zero, NULL, maximum scale, Unicode, time-zone transitions, and binary zero bytes. For lossy conversions, document exactly which information is intentionally discarded.
Performance and Query Plans
Implicit conversion can appear only as a plan expression or warning. It consumes CPU, distorts cardinality estimates, and may shift a query from an index seek to a scan. The effect grows with row count and execution frequency.
Inspect execution plans when columns and parameters differ, when joins combine different schemas, or when predicates call CAST. Fix the data model or parameter binding before adding indexes to compensate for conversion overhead.
Conversion and Constraints
After migration, constraints should protect the clean representation. NOT NULL preserves required presence, CHECK enforces range, UNIQUE preserves identity, and foreign keys ensure compatible relationships. Conversion should lead to stronger typing, not merely a different storage syntax.
Add constraints only after rejected rows are understood. Automatically replacing invalid values with defaults can satisfy a constraint while corrupting meaning.
Common Conversion Mistakes
- Relying on implicit conversion in important comparisons.
- Casting indexed columns instead of binding typed parameters.
- Converting timestamp columns to text or date for filtering.
- Sorting numeric data through its character representation.
- Converting exact money to floating point.
- Assuming CAST is lossless or consistent across databases.
- Using runtime conversion to hide a bad schema.
- Ignoring invalid import rows returned as NULL by safe parsing.
- Confusing conversion with localized formatting.
- Converting everything to VARCHAR for easy integration.
- Destroying raw source data before validation is complete.
- Changing a production column type without dependency and rollback plans.
Conversion Best Practices
- Store data in the type that matches its business meaning.
- Prefer explicit CAST when a conversion is intentional.
- Convert once at a system boundary rather than in every query.
- Check range, length, precision, scale, and format before narrowing.
- State rounding and time-zone behavior explicitly.
- Preserve raw imports and rejected-value reasons.
- Use unambiguous machine formats for temporal interchange.
- Bind application parameters with native types.
- Keep functions off indexed columns in search predicates.
- Verify edge cases and source-target-source round trips.
Performance Checklist
- Do column and parameter types match?
- Does a WHERE or JOIN expression cast an indexed column?
- Does the plan contain implicit-conversion warnings?
- Can a typed range replace date conversion?
- Can staging or a generated clean column perform conversion once?
- Are aggregate input types wide enough before calculation?
Data Quality Checklist
- Are raw and cleaned values both traceable?
- Are invalid syntax, missing values, and out-of-range values distinguished?
- Are leading zeros and identifier semantics protected?
- Are rounding, truncation, and character-loss counts reported?
- Are accepted plus rejected rows reconciled to the source?
- Are business aggregates compared before and after conversion?
Real-World Scenario: Legacy Price Text
A legacy table stores price values such as '19.95', '1,299.00', empty text, and 'N/A' in one VARCHAR column. Casting the column inside every report is unsafe because formatting differs and one invalid row can stop the query. It also keeps financial values outside exact numeric constraints.
A safer process copies the raw values into staging, classifies missing and invalid records, removes only approved separators, converts valid values to DECIMAL with the required scale, and reconciles counts and totals. Production then stores DECIMAL plus a currency code. The original text remains available for audit until migration acceptance is complete.
Real-World Scenario: Legacy Date Text
A customer system contains dates in ISO, month-day-year, day-month-year, and empty formats. Guessing from punctuation is unreliable because values such as 03/04/2026 are inherently ambiguous. Conversion must use source metadata or an agreed format per feed, not the database session locale.
The migration records source system, original string, selected format, parsed date, and validation status. Impossible or ambiguous values are quarantined for resolution. After acceptance, the application writes a native DATE and the API publishes one documented interchange format. This removes repeated parsing from filters and reports.
Real-World Scenario: INTEGER to BIGINT
An event table approaching the INTEGER limit requires more than an ALTER statement. Its identifier may appear in foreign keys, indexes, partitions, materialized views, event payloads, caches, and application classes. Every dependent representation must support the larger range before new values cross the old limit.
Teams should forecast the exhaustion date, rehearse the widening operation, update dependent columns and application mappings, and monitor replication and lock duration. During a phased release, old clients must not receive values they cannot represent. The conversion is logically lossless, but the deployment can still fail if one system boundary remains narrow.
Interview Questions and Answers
What is data type conversion? It changes a value from one SQL type to another compatible type.
What are the two conversion methods? Explicit conversion is requested in SQL; implicit conversion is selected automatically by the engine.
What is CAST? CAST is the standard SQL expression for explicitly converting an expression to a target type.
Can conversion lose data? Yes. Narrower range, lower scale, shorter length, reduced temporal detail, or approximate representation can lose information.
Why can implicit conversion be dangerous? Its direction can be unexpected, fail on data, change results, and prevent index use.
Why avoid casting an indexed column? The computed expression can make the predicate non-sargable and cause a scan.
What is type precedence? It is the database rule that chooses a common type when unlike types appear in one expression.
Can CAST fix a bad schema? It can provide a temporary boundary, but repeated conversion does not replace correct stored types and constraints.
What is a lossy conversion? It is a conversion where the target cannot reproduce all information in the source.
What is the safest import pattern? Preserve raw data in staging, validate it, convert explicitly, quarantine failures, and reconcile results.
Complete Conversion Workflow
- Understand the source value's business meaning.
- Identify its actual source type and representation.
- Select the truthful target type.
- Validate source syntax and encoding.
- Check target range, length, precision, and scale.
- Define allowed loss, rounding, and time-zone behavior.
- Perform an explicit conversion in a controlled boundary.
- Capture failures without confusing them with NULL input.
- Validate counts, values, aggregates, and query plans.
- Preserve the correct type through storage and application layers.
Key Takeaway
SQL conversion is safe when the source meaning is understood, the target can represent the required information, the conversion is explicit, and the result is validated. A successful CAST only proves that an engine produced a target value; it does not prove that the value is complete or correct.
SAFE CONVERSION = SOURCE SEMANTICS
+ EXPLICIT TARGET TYPE
+ RANGE AND PRECISION CHECKS
+ DEFINED LOSS POLICY
+ PERFORMANCE-AWARE PLACEMENT
+ RESULT VALIDATION
Use conversion at deliberate boundaries, preserve raw evidence, bind typed parameters, avoid functions on indexed columns, and migrate schemas through measured stages. The strongest long-term solution is still to store each fact in the correct type from the beginning.