One bad text value can stop a load that otherwise looks ready. Finding rows that will not convert gives you a rejection list before the INSERT begins. Use the destination types and explicit date rules, then load only the rows that satisfy both conversion and required-value checks.
Find Values That Will Not Convert Apart From Missing Input
TRY_CAST and TRY_CONVERT return NULL when a supported conversion fails. That makes them useful for finding bad staging values without stopping the whole inspection query. SQL Server 2012 and later provide these functions. An original NULL also returns NULL, so distinguish missing input from failed conversion.
I inspect each destination column separately before loading. A row can have more than one invalid value, and one overall rejected-row count does not explain which source fields need correction. Keep a stable staging identifier with every finding.
The example uses synthetic date and amount text. The first check follows the simple pattern: conversion is NULL while source text is not NULL. It exposes conversion failures but does not reject every unwanted blank or default interpretation. Empty strings and whitespace need a separate normalization policy. The load should not discover its first bad value halfway through an important transaction.
CREATE TABLE #TextStage(SourceID int PRIMARY KEY,DateText nvarchar(40),AmountText nvarchar(40));
INSERT #TextStage VALUES(1,N'20260924',N'12.50'),(2,N'bad date',N'10.00'),
(3,N'20261301',N'bad amount'),(4,NULL,N'9.00');
SELECT SourceID,N'DateText' AS BadColumn,DateText AS BadValue
FROM #TextStage WHERE TRY_CAST(DateText AS date) IS NULL AND DateText IS NOT NULL
UNION ALL
SELECT SourceID,N'AmountText',AmountText
FROM #TextStage WHERE TRY_CAST(AmountText AS decimal(12,2)) IS NULL AND AmountText IS NOT NULL;Preserve the original value when it will not convert, so a rejected row remains explainable and repairable.
Normalize Blanks and Specify the Date Style
The source contract should define accepted formats. TRY_CONVERT accepts a style argument for dates, which is more deliberate than relying on session language or DATEFORMAT for ambiguous input. The next example uses style 112 for the compact year-month-day date format in the valid source row.
NULLIF over trimmed text treats an empty normalized value as missing. That is a business choice, so preserve the original text beside the parsed result. Do not replace meaningful internal spaces or punctuation with a broad cleanup that changes the source's value.
I materialize the classified staging result when several later steps need the same interpretation. That keeps the accepted and rejected lists based on the same conversion expressions. A repeated independent parser can drift from the one used by the INSERT. The temporary table below retains both raw fields and their typed versions so an exception remains explainable after the clean rows are loaded.
SELECT SourceID,DateText,AmountText,
TRY_CONVERT(date,NULLIF(LTRIM(RTRIM(DateText)),N''),112) AS ParsedDate,
TRY_CONVERT(decimal(12,2),NULLIF(LTRIM(RTRIM(AmountText)),N'')) AS ParsedAmount
INTO #ConvertedStage
FROM #TextStage;
SELECT SourceID,DateText,AmountText,ParsedDate,ParsedAmount
FROM #ConvertedStage WHERE ParsedDate IS NULL OR ParsedAmount IS NULL;Load Only Rows That Meet the Required Contract
The destination in this demonstration requires both date and amount. Rows with a NULL parsed value are rejected, whether the cause is missing input or failed conversion. A different destination allowing NULL needs a different acceptance predicate. Conversion success alone does not define the whole load rule.
The next INSERT selects only the classified clean rows. The final query reports rejected rows with field-level status. Keep those rows available for correction and replay instead of silently discarding them. The source identifier connects the report with the original input.
What should happen to a row after it is corrected? Define the replay and duplicate policy before rerunning the load. A primary key protects the destination identity, but repeated execution still needs controlled handling. Also validate numeric range, permitted dates, and other business rules after parsing. A valid date in the wrong business period remains a bad load value even though TRY_CONVERT accepts it.
CREATE TABLE #CleanLoad(SourceID int PRIMARY KEY,EventDate date NOT NULL,Amount decimal(12,2) NOT NULL);
INSERT #CleanLoad
SELECT SourceID,ParsedDate,ParsedAmount FROM #ConvertedStage
WHERE ParsedDate IS NOT NULL AND ParsedAmount IS NOT NULL;
SELECT SourceID,
CASE WHEN DateText IS NULL THEN 'Missing date' WHEN ParsedDate IS NULL THEN 'Invalid date' ELSE 'Accepted date' END AS DateStatus,
CASE WHEN AmountText IS NULL THEN 'Missing amount' WHEN ParsedAmount IS NULL THEN 'Invalid amount' ELSE 'Accepted amount' END AS AmountStatus
FROM #ConvertedStage WHERE ParsedDate IS NULL OR ParsedAmount IS NULL;
Use Culture Parsing Only When the Source Needs It
TRY_PARSE can interpret formatted text using a named culture. That is useful when a source supplies localized numeric or date presentation rather than a stable machine format. The next example uses a synthetic American-formatted number with an explicit culture.
TRY_PARSE relies on CLR parsing and has more overhead than a straightforward supported conversion. Use it where the culture rule is genuinely required, then measure the cost on representative input. Do not apply it to every staging column simply because it accepted one convenient sample.
Prefer a stable export contract with unambiguous machine-formatted values when the producer can supply one. A number displayed for a person is not always an ideal interchange value. Keep the culture with the source definition and test separators, signs, and precision. Do not strip all punctuation and assume the remaining digits retain the original magnitude or sign.
SELECT TRY_PARSE(N'1,234.56' AS decimal(12,2) USING 'en-US') AS ParsedFormattedAmount;Know Which Errors TRY_CAST Does Not Suppress
TRY_CAST does not make explicitly forbidden conversions valid. Converting an int directly to xml is a documented example that still raises an error. That behavior differs from malformed text submitted to a supported conversion.
The next block is meant to fail. It runs that deliberate error in a lower dynamic batch, and the CATCH block returns error 529 with its message. It demonstrates the limit without pretending the function returns NULL for every possible type combination. Keep error handling around the load's real supported operations as well.
Long max-type text and destination precision have their own supported limits. Review the function's input restrictions when the staging design contains unusually large values. A small nvarchar example does not prove every large input is supported. The inspection needs to match the actual destination types and source widths, because parsing under a wider demonstration type can conceal the failure the real INSERT would encounter.
BEGIN TRY
EXEC sys.sp_executesql N'SELECT TRY_CAST(4 AS xml) AS ConvertedValue;';
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;Reconcile Rows That Will Not Convert With Accepted Rows
Verify that every staging row is either accepted or retained with an explicit rejection reason. A row failing two columns belongs to one rejected-row population but can have two field-level findings. Keep those counts conceptually separate when reporting the load.
Test valid values, invalid values, NULL, blanks, precision overflow, and every supported source format. Then check the actual clean destination values, not only the absence of an exception. A conversion can round or normalize a value under its documented type rules.
Finding rows that will not convert makes the load predictable when the parser, required-value policy, and replay path are explicit. Preserve raw input, classify it once, load the valid set, and report the rest. The result is a load whose exceptions are visible before they become a surprise.
Related reading on this blog: Implicit Conversions That Quietly Turn Seeks Into Scans and CONVERT Empty String To Null DateTime.
A successful conversion is not complete validation, it is one check against the destination contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
