3

We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering

As part of this effort, we are taking data from the base Entity table and dumping it into Entity_New if all the data can be correctly converted to the appropriate type. Otherwise, it goes into a copy of the existing table named Entity_Bad.

We have a rules engine that validated the data and types as it when in so in theory, the data should be clean even though it's stored in character fields. The reality is my posting here because some thing's off and I'll can't find it. The CompletionDate field in Entity is varchar(46) NULL

Environment is

productversion  productlevel   edition
10.0.4064.0     SP2            Enterprise Edition (64-bit)

My scripts demonstrating what I was doing and what is but isn't working

SET NOCOUNT ON

DECLARE
    @startid int = 0
,   @stopid int = 796833


-------------------------------------------------------------------------------
-- Check doesn't find anything wrong with CompletionDate
-------------------------------------------------------------------------------
SELECT
    1
FROM
    [dbo].[Entity] E
    INNER JOIN
        dbo.EntityBatch_New PB
        ON E.FiscalYear = PB.FiscalYear
            AND E.HashCode = PB.HashCode
            AND E.AAKey = PB.AAKey
            AND PB.ProcessResultCode IN ('A','W','M')
WHERE
    PB.EntityBatchId BETWEEN @StartId AND @StopId
    AND
    (
        -- check
        (isDate(E.[CompletionDate]) = 0 AND E.[CompletionDate] IS NOT NULL)
        AND (isDate(E.[CompletionDate]) = 1 AND CAST(E.[CompletionDate] AS datetime) BETWEEN '1753-01-01T00:00:00.000' AND '9999-12-31T23:59:59.997')
    )

-------------------------------------------------------------------------------
-- Only row that shows as non-date is the NULL one, which is expected
-------------------------------------------------------------------------------
SELECT DISTINCT
    (E.[CompletionDate] )
,  isDate(E.[CompletionDate])
FROM
    [dbo].[Entity] E
    INNER JOIN
        dbo.EntityBatch_New PB
        ON E.FiscalYear = PB.FiscalYear
            AND E.HashCode = PB.HashCode
            AND PB.ProcessResultCode IN ('A','W','M')
    -- Ensure we aren't pulling something we have already processed
    LEFT OUTER JOIN
        [dbo].[Entity_new] N
        ON N.HashCode = E.HashCode
            AND N.FiscalYear = E.FiscalYear
                AND E.AAKey = N.AAKey
    -- Ensure we aren't pulling something we have already processed (or was bad)
    LEFT OUTER JOIN
        [dbo].[Entity_bad] BAD
        ON BAD.HashCode = E.HashCode
            AND BAD.FiscalYear = E.FiscalYear
                AND E.AAKey = BAD.AAKey
WHERE
    PB.EntityBatchId BETWEEN @StartId AND @StopId
    AND N.FiscalYear IS NULL
    AND BAD.FiscalYear IS NULL
ORDER BY 2

-------------------------------------------------------------------------------
-- Make the cast and it blows with
-- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
-------------------------------------------------------------------------------
SELECT DISTINCT
    (E.[CompletionDate] )
,   CAST(E.[CompletionDate] AS datetime) AS [CompletionDate]
FROM
    [dbo].[Entity] E
    INNER JOIN
        dbo.EntityBatch_New PB
        ON E.FiscalYear = PB.FiscalYear
            AND E.HashCode = PB.HashCode
            AND PB.ProcessResultCode IN ('A','W','M')
    -- Ensure we aren't pulling something we have already processed
    LEFT OUTER JOIN
        [dbo].[Entity_new] N
        ON N.HashCode = E.HashCode
            AND N.FiscalYear = E.FiscalYear
                AND E.AAKey = N.AAKey
    -- Ensure we aren't pulling something we have already processed (or was bad)
    LEFT OUTER JOIN
        [dbo].[Entity_bad] BAD
        ON BAD.HashCode = E.HashCode
            AND BAD.FiscalYear = E.FiscalYear
                AND E.AAKey = BAD.AAKey
WHERE
    PB.EntityBatchId BETWEEN @StartId AND @StopId
    AND N.FiscalYear IS NULL
    AND BAD.FiscalYear IS NULL


-------------------------------------------------------------------------------
-- Dump the values into a temporary table to slice and dice the values
-------------------------------------------------------------------------------
DECLARE @debug TABLE
(
    CompletionDate varchar(46) NULL
)
INSERT INTO
    @debug
SELECT DISTINCT
    (E.[CompletionDate] )
FROM
    [dbo].[Entity] E
    INNER JOIN
        dbo.EntityBatch_New PB
        ON E.FiscalYear = PB.FiscalYear
            AND E.HashCode = PB.HashCode
            AND PB.ProcessResultCode IN ('A','W','M')
    -- Ensure we aren't pulling something we have already processed
    LEFT OUTER JOIN
        [dbo].[Entity_new] N
        ON N.HashCode = E.HashCode
            AND N.FiscalYear = E.FiscalYear
                AND E.AAKey = N.AAKey
    -- Ensure we aren't pulling something we have already processed (or was bad)
    LEFT OUTER JOIN
        [dbo].[Entity_bad] BAD
        ON BAD.HashCode = E.HashCode
            AND BAD.FiscalYear = E.FiscalYear
                AND E.AAKey = BAD.AAKey
WHERE
    PB.EntityBatchId BETWEEN @StartId AND @StopId
    AND N.FiscalYear IS NULL
    AND BAD.FiscalYear IS NULL


-------------------------------------------------------------------------------
-- This is operating on all the same values as the failing query but magically works
-------------------------------------------------------------------------------
SELECT ALL
    CAST(E.[CompletionDate] AS datetime) AS [CompletionDate]
FROM
    @debug E



-------------------------------------------------------------------------------
-- Clearly, something is amiss when we extract the data so process each row
-- and find the culprit that way. Except this finds nothing wrong
-------------------------------------------------------------------------------
DECLARE @hash uniqueidentifier
,   @zee_date varchar(46)
,   @real_date datetime

DECLARE
    CSR CURSOR READ_ONLY
FOR
SELECT
    E.HashCode
,   E.[CompletionDate]
FROM
    [dbo].[Entity] E
    INNER JOIN
        dbo.EntityBatch_New PB
        ON E.FiscalYear = PB.FiscalYear
            AND E.HashCode = PB.HashCode
            AND PB.ProcessResultCode IN ('A','W','M')
    -- Ensure we aren't pulling something we have already processed
    LEFT OUTER JOIN
        [dbo].[Entity_new] N
        ON N.HashCode = E.HashCode
            AND N.FiscalYear = E.FiscalYear
                AND E.AAKey = N.AAKey
    -- Ensure we aren't pulling something we have already processed (or was bad)
    LEFT OUTER JOIN
        [dbo].[Entity_bad] BAD
        ON BAD.HashCode = E.HashCode
            AND BAD.FiscalYear = E.FiscalYear
                AND E.AAKey = BAD.AAKey
WHERE
    PB.EntityBatchId BETWEEN @StartId AND @StopId
    AND N.FiscalYear IS NULL
    AND BAD.FiscalYear IS NULL


OPEN CSR

FETCH NEXT FROM CSR INTO
    @hash, @zee_date

WHILE (@@fetch_status = 0)
BEGIN
    BEGIN TRY
        SELECT @real_date = cast(@zee_date AS datetime)
    END TRY
    BEGIN CATCH
        print 'In here'
        print @hash
        print @zee_date
        SELECT @hash, @zee_date
    END CATCH


    FETCH NEXT FROM CSR INTO
        @hash, @zee_date

END

CLOSE csr
DEALLOCATE csr

After critting you with with the above wall of code, here are the 406 unique values the above queries are operating on.

DECLARE @REAL_DATES TABLE
(
    CompletionDate varchar(46) NULL
)

INSERT INTO
    @REAL_DATES 
SELECT
    NULL
UNION ALL SELECT '19000101'
UNION ALL SELECT '20100208'
UNION ALL SELECT '20100228'
UNION ALL SELECT '20100309'
UNION ALL SELECT '20100314'
UNION ALL SELECT '20100401'
UNION ALL SELECT '20100409'
UNION ALL SELECT '20100420'
UNION ALL SELECT '20100427'
UNION ALL SELECT '20100429'
UNION ALL SELECT '20100507'
UNION ALL SELECT '20100615'
UNION ALL SELECT '20100617'
UNION ALL SELECT '20100629'
UNION ALL SELECT '20100701'
UNION ALL SELECT '20100703'
UNION ALL SELECT '20100704'
UNION ALL SELECT '20100706'
UNION ALL SELECT '20100709'
UNION ALL SELECT '20100713'
UNION ALL SELECT '20100714'
UNION ALL SELECT '20100715'
UNION ALL SELECT '20100716'
UNION ALL SELECT '20100720'
UNION ALL SELECT '20100721'
UNION ALL SELECT '20100726'
UNION ALL SELECT '20100727'
UNION ALL SELECT '20100728'
UNION ALL SELECT '20100729'
UNION ALL SELECT '20100731'
UNION ALL SELECT '20100801'
UNION ALL SELECT '20100802'
UNION ALL SELECT '20100803'
UNION ALL SELECT '20100804'
UNION ALL SELECT '20100807'
UNION ALL SELECT '20100809'
UNION ALL SELECT '20100810'
UNION ALL SELECT '20100811'
UNION ALL SELECT '20100813'
UNION ALL SELECT '20100817'
UNION ALL SELECT '20100819'
UNION ALL SELECT '20100820'
UNION ALL SELECT '20100822'
UNION ALL SELECT '20100823'
UNION ALL SELECT '20100825'
UNION ALL SELECT '20100827'
UNION ALL SELECT '20100828'
UNION ALL SELECT '20100830'
UNION ALL SELECT '20100831'
UNION ALL SELECT '20100901'
UNION ALL SELECT '20100902'
UNION ALL SELECT '20100904'
UNION ALL SELECT '20100907'
UNION ALL SELECT '20100908'
UNION ALL SELECT '20100909'
UNION ALL SELECT '20100910'
UNION ALL SELECT '20100911'
UNION ALL SELECT '20100913'
UNION ALL SELECT '20100916'
UNION ALL SELECT '20100919'
UNION ALL SELECT '20100920'
UNION ALL SELECT '20100922'
UNION ALL SELECT '20100923'
UNION ALL SELECT '20100925'
UNION ALL SELECT '20100928'
UNION ALL SELECT '20101002'
UNION ALL SELECT '20101004'
UNION ALL SELECT '20101007'
UNION ALL SELECT '20101009'
UNION ALL SELECT '20101010'
UNION ALL SELECT '20101013'
UNION ALL SELECT '20101016'
UNION ALL SELECT '20101018'
UNION ALL SELECT '20101019'
UNION ALL SELECT '20101020'
UNION ALL SELECT '20101022'
UNION ALL SELECT '20101023'
UNION ALL SELECT '20101025'
UNION ALL SELECT '20101028'
UNION ALL SELECT '20101030'
UNION ALL SELECT '20101102'
UNION ALL SELECT '20101107'
UNION ALL SELECT '20101108'
UNION ALL SELECT '20101109'
UNION ALL SELECT '20101111'
UNION ALL SELECT '20101112'
UNION ALL SELECT '20101114'
UNION ALL SELECT '20101117'
UNION ALL SELECT '20101119'
UNION ALL SELECT '20101124'
UNION ALL SELECT '20101126'
UNION ALL SELECT '20101127'
UNION ALL SELECT '20101129'
UNION ALL SELECT '20101201'
UNION ALL SELECT '20101203'
UNION ALL SELECT '20101204'
UNION ALL SELECT '20101206'
UNION ALL SELECT '20101209'
UNION ALL SELECT '20101210'
UNION ALL SELECT '20101213'
UNION ALL SELECT '20101214'
UNION ALL SELECT '20101215'
UNION ALL SELECT '20101216'
UNION ALL SELECT '20101220'
UNION ALL SELECT '20101222'
UNION ALL SELECT '20101227'
UNION ALL SELECT '20101228'
UNION ALL SELECT '20101229'
UNION ALL SELECT '20101230'
UNION ALL SELECT '20110102'
UNION ALL SELECT '20110103'
UNION ALL SELECT '20110104'
UNION ALL SELECT '20110106'
UNION ALL SELECT '20110107'
UNION ALL SELECT '20110108'
UNION ALL SELECT '20110109'
UNION ALL SELECT '20110110'
UNION ALL SELECT '20110111'
UNION ALL SELECT '20110114'
UNION ALL SELECT '20110116'
UNION ALL SELECT '20110118'
UNION ALL SELECT '20110119'
UNION ALL SELECT '20110120'
UNION ALL SELECT '20110122'
UNION ALL SELECT '20110123'
UNION ALL SELECT '20110125'
UNION ALL SELECT '20110126'
UNION ALL SELECT '20110128'
UNION ALL SELECT '20110130'
UNION ALL SELECT '20110203'
UNION ALL SELECT '20110205'
UNION ALL SELECT '20110206'
UNION ALL SELECT '20110208'
UNION ALL SELECT '20110210'
UNION ALL SELECT '20110212'
UNION ALL SELECT '20110213'
UNION ALL SELECT '20110215'
UNION ALL SELECT '20110218'
UNION ALL SELECT '20110221'
UNION ALL SELECT '20110224'
UNION ALL SELECT '20110226'
UNION ALL SELECT '20110301'
UNION ALL SELECT '20110302'
UNION ALL SELECT '20110304'
UNION ALL SELECT '20110307'
UNION ALL SELECT '20110309'
UNION ALL SELECT '20110311'
UNION ALL SELECT '20110314'
UNION ALL SELECT '20110316'
UNION ALL SELECT '20110317'
UNION ALL SELECT '20110320'
UNION ALL SELECT '20110321'
UNION ALL SELECT '20110323'
UNION ALL SELECT '20110326'
UNION ALL SELECT '20110328'
UNION ALL SELECT '20110329'
UNION ALL SELECT '20110331'
UNION ALL SELECT '20110403'
UNION ALL SELECT '20110405'
UNION ALL SELECT '20110406'
UNION ALL SELECT '20110408'
UNION ALL SELECT '20110410'
UNION ALL SELECT '20110415'
UNION ALL SELECT '20110416'
UNION ALL SELECT '20110417'
UNION ALL SELECT '20110418'
UNION ALL SELECT '20110421'
UNION ALL SELECT '20110422'
UNION ALL SELECT '20110423'
UNION ALL SELECT '20110426'
UNION ALL SELECT '20110429'
UNION ALL SELECT '20110501'
UNION ALL SELECT '20110503'
UNION ALL SELECT '20110504'
UNION ALL SELECT '20110506'
UNION ALL SELECT '20110508'
UNION ALL SELECT '20110509'
UNION ALL SELECT '20110511'
UNION ALL SELECT '20110512'
UNION ALL SELECT '20110514'
UNION ALL SELECT '20110517'
UNION ALL SELECT '20110518'
UNION ALL SELECT '20110519'
UNION ALL SELECT '20110520'
UNION ALL SELECT '20110522'
UNION ALL SELECT '20110526'
UNION ALL SELECT '20110531'
UNION ALL SELECT '20110603'
UNION ALL SELECT '20110604'
UNION ALL SELECT '20110605'
UNION ALL SELECT '20110606'
UNION ALL SELECT '20110608'
UNION ALL SELECT '20110611'
UNION ALL SELECT '20110613'
UNION ALL SELECT '20110614'
UNION ALL SELECT '20110616'
UNION ALL SELECT '20110622'
UNION ALL SELECT '20110624'
UNION ALL SELECT '20110627'
UNION ALL SELECT '20110703'
UNION ALL SELECT '20110704'
UNION ALL SELECT '20110711'
UNION ALL SELECT '20110712'
UNION ALL SELECT '20110713'
UNION ALL SELECT '20110714'
UNION ALL SELECT '20110719'
UNION ALL SELECT '20110720'
UNION ALL SELECT '20110725'
UNION ALL SELECT '20110726'
UNION ALL SELECT '20110802'
UNION ALL SELECT '20110804'
UNION ALL SELECT '20110811'
UNION ALL SELECT '20090611'
UNION ALL SELECT '20091124'
UNION ALL SELECT '20100201'
UNION ALL SELECT '20100202'
UNION ALL SELECT '20100204'
UNION ALL SELECT '20100220'
UNION ALL SELECT '20100305'
UNION ALL SELECT '20100323'
UNION ALL SELECT '20100414'
UNION ALL SELECT '20100417'
UNION ALL SELECT '20100508'
UNION ALL SELECT '20100512'
UNION ALL SELECT '20100527'
UNION ALL SELECT '20100616'
UNION ALL SELECT '20100702'
UNION ALL SELECT '20100705'
UNION ALL SELECT '20100707'
UNION ALL SELECT '20100708'
UNION ALL SELECT '20100710'
UNION ALL SELECT '20100711'
UNION ALL SELECT '20100712'
UNION ALL SELECT '20100717'
UNION ALL SELECT '20100719'
UNION ALL SELECT '20100722'
UNION ALL SELECT '20100723'
UNION ALL SELECT '20100724'
UNION ALL SELECT '20100725'
UNION ALL SELECT '20100730'
UNION ALL SELECT '20100805'
UNION ALL SELECT '20100806'
UNION ALL SELECT '20100808'
UNION ALL SELECT '20100812'
UNION ALL SELECT '20100814'
UNION ALL SELECT '20100815'
UNION ALL SELECT '20100816'
UNION ALL SELECT '20100818'
UNION ALL SELECT '20100821'
UNION ALL SELECT '20100824'
UNION ALL SELECT '20100826'
UNION ALL SELECT '20100829'
UNION ALL SELECT '20100903'
UNION ALL SELECT '20100906'
UNION ALL SELECT '20100912'
UNION ALL SELECT '20100914'
UNION ALL SELECT '20100915'
UNION ALL SELECT '20100917'
UNION ALL SELECT '20100918'
UNION ALL SELECT '20100921'
UNION ALL SELECT '20100924'
UNION ALL SELECT '20100926'
UNION ALL SELECT '20100927'
UNION ALL SELECT '20100929'
UNION ALL SELECT '20100930'
UNION ALL SELECT '20101001'
UNION ALL SELECT '20101003'
UNION ALL SELECT '20101005'
UNION ALL SELECT '20101006'
UNION ALL SELECT '20101008'
UNION ALL SELECT '20101011'
UNION ALL SELECT '20101012'
UNION ALL SELECT '20101014'
UNION ALL SELECT '20101015'
UNION ALL SELECT '20101017'
UNION ALL SELECT '20101021'
UNION ALL SELECT '20101024'
UNION ALL SELECT '20101026'
UNION ALL SELECT '20101027'
UNION ALL SELECT '20101029'
UNION ALL SELECT '20101031'
UNION ALL SELECT '20101101'
UNION ALL SELECT '20101103'
UNION ALL SELECT '20101104'
UNION ALL SELECT '20101105'
UNION ALL SELECT '20101106'
UNION ALL SELECT '20101110'
UNION ALL SELECT '20101113'
UNION ALL SELECT '20101115'
UNION ALL SELECT '20101116'
UNION ALL SELECT '20101118'
UNION ALL SELECT '20101120'
UNION ALL SELECT '20101122'
UNION ALL SELECT '20101123'
UNION ALL SELECT '20101125'
UNION ALL SELECT '20101128'
UNION ALL SELECT '20101130'
UNION ALL SELECT '20101202'
UNION ALL SELECT '20101205'
UNION ALL SELECT '20101207'
UNION ALL SELECT '20101208'
UNION ALL SELECT '20101212'
UNION ALL SELECT '20101217'
UNION ALL SELECT '20101218'
UNION ALL SELECT '20101219'
UNION ALL SELECT '20101221'
UNION ALL SELECT '20101223'
UNION ALL SELECT '20101224'
UNION ALL SELECT '20101226'
UNION ALL SELECT '20101231'
UNION ALL SELECT '20110101'
UNION ALL SELECT '20110105'
UNION ALL SELECT '20110112'
UNION ALL SELECT '20110113'
UNION ALL SELECT '20110115'
UNION ALL SELECT '20110117'
UNION ALL SELECT '20110121'
UNION ALL SELECT '20110124'
UNION ALL SELECT '20110127'
UNION ALL SELECT '20110129'
UNION ALL SELECT '20110131'
UNION ALL SELECT '20110201'
UNION ALL SELECT '20110202'
UNION ALL SELECT '20110204'
UNION ALL SELECT '20110207'
UNION ALL SELECT '20110209'
UNION ALL SELECT '20110211'
UNION ALL SELECT '20110214'
UNION ALL SELECT '20110216'
UNION ALL SELECT '20110217'
UNION ALL SELECT '20110219'
UNION ALL SELECT '20110220'
UNION ALL SELECT '20110222'
UNION ALL SELECT '20110223'
UNION ALL SELECT '20110225'
UNION ALL SELECT '20110228'
UNION ALL SELECT '20110303'
UNION ALL SELECT '20110305'
UNION ALL SELECT '20110306'
UNION ALL SELECT '20110308'
UNION ALL SELECT '20110310'
UNION ALL SELECT '20110312'
UNION ALL SELECT '20110313'
UNION ALL SELECT '20110315'
UNION ALL SELECT '20110318'
UNION ALL SELECT '20110322'
UNION ALL SELECT '20110324'
UNION ALL SELECT '20110325'
UNION ALL SELECT '20110327'
UNION ALL SELECT '20110330'
UNION ALL SELECT '20110401'
UNION ALL SELECT '20110404'
UNION ALL SELECT '20110407'
UNION ALL SELECT '20110409'
UNION ALL SELECT '20110411'
UNION ALL SELECT '20110412'
UNION ALL SELECT '20110413'
UNION ALL SELECT '20110414'
UNION ALL SELECT '20110419'
UNION ALL SELECT '20110420'
UNION ALL SELECT '20110425'
UNION ALL SELECT '20110427'
UNION ALL SELECT '20110428'
UNION ALL SELECT '20110430'
UNION ALL SELECT '20110502'
UNION ALL SELECT '20110505'
UNION ALL SELECT '20110507'
UNION ALL SELECT '20110510'
UNION ALL SELECT '20110513'
UNION ALL SELECT '20110515'
UNION ALL SELECT '20110516'
UNION ALL SELECT '20110521'
UNION ALL SELECT '20110523'
UNION ALL SELECT '20110524'
UNION ALL SELECT '20110525'
UNION ALL SELECT '20110527'
UNION ALL SELECT '20110528'
UNION ALL SELECT '20110530'
UNION ALL SELECT '20110601'
UNION ALL SELECT '20110602'
UNION ALL SELECT '20110607'
UNION ALL SELECT '20110609'
UNION ALL SELECT '20110610'
UNION ALL SELECT '20110615'
UNION ALL SELECT '20110617'
UNION ALL SELECT '20110618'
UNION ALL SELECT '20110620'
UNION ALL SELECT '20110621'
UNION ALL SELECT '20110623'
UNION ALL SELECT '20110626'
UNION ALL SELECT '20110628'
UNION ALL SELECT '20110629'
UNION ALL SELECT '20110630'
UNION ALL SELECT '20110701'
UNION ALL SELECT '20110706'
UNION ALL SELECT '20110707'
UNION ALL SELECT '20110708'
UNION ALL SELECT '20110715'
UNION ALL SELECT '20110717'
UNION ALL SELECT '20110721'
UNION ALL SELECT '20110722'
UNION ALL SELECT '20110727'
UNION ALL SELECT '20110729'
UNION ALL SELECT '20110801'
UNION ALL SELECT '20110810'

SELECT 
    CAST(RD.CompletionDate AS datetime) AS casts_fine
FROm
    @REAL_DATES RD

I do appreciate the comments about SSIS or other approaches but we're married to the TSQL conversion approach at this point in the game. If anyone can point out what I'm missing, I'll name my first child after you, assuming you don't mind changing your name to James.

Improve this question
0

1 Answer 1

Reset to default
8

What happens when you do this?

DECLARE @REAL_DATES TABLE
(
    CompletionDate VARCHAR(46)
);

INSERT INTO
    @REAL_DATES
    SELECT CompletionDate
    FROM dbo.Entity;

SELECT 
    CAST(RD.CompletionDate AS datetime) AS casts_fine
    FROM @REAL_DATES RD;

What I was getting at on twitter was that the conversion can be attempted by the optimizer before rows are eliminated, so you can't only consider the CompletionDate values that are returned by the join.

My first suggestion would be to use the right data type. Why are you using VARCHAR(46) to store a date? This is why you have bad data in the table and why you have to explicitly convert when you want rich data that is not a string (and should never have been a string in the first place IMHO).

My next suggestion would be to correct ALL data in that column, and put measures in place so that it won't become invalid again. For example, a check constraint that validates ISDATE(columnname) = 1.

Failing those two, next on my list would be to return the data to the client and let it convert to datetime or for display or what have you. No matter where you filter out the rows that are causing the problem, the optimizer could push that evaluation around so that the convert is attempted before the bad rows are weeded out.

And finally, you could dump the result of the query into a temp table / table variable, and perform the convert as a second step when querying that intermediate object (since you should be confident that the dates here are valid - in fact you can check first and raise an error if your join happened to return some rows with invalid dates).

Bottom line: (a) you can't make any assumptions about where in the stack a conversion attempt will be made and (b) these workarounds and hacks would not be necessary if you used the right data type.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.