Estamos no processo de mover os dados de uma tabela herdada (todos os campos varchar) para uma sugestão de contraparte fortemente tipada cheering
Como parte desse esforço, estamos pegando dados da tabela Entity base e despejando-os em Entity_New se todos os dados puderem ser convertidos corretamente para o tipo apropriado. Caso contrário, ele vai para uma cópia da tabela existente chamada Entity_Bad.
Temos um mecanismo de regras que validou os dados e os tipos quando, em teoria, os dados devem estar limpos, mesmo que sejam armazenados em campos de caracteres. A realidade é que estou postando aqui porque alguma coisa está errada e não consigo encontrá-la. O campo CompletionDate em Entity é varchar(46) NULL
Ambiente é
productversion productlevel edition
10.0.4064.0 SP2 Enterprise Edition (64-bit)
Meus scripts demonstrando o que eu estava fazendo e o que está, mas não está funcionando
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
Depois de criticar você com a parede de código acima, aqui estão os 406 valores exclusivos nos quais as consultas acima estão operando.
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
Eu aprecio os comentários sobre SSIS ou outras abordagens, mas estamos casados com a abordagem de conversão TSQL neste ponto do jogo. Se alguém puder apontar o que estou perdendo, darei seu nome ao meu primeiro filho, supondo que você não se importe em mudar seu nome para James.
O que acontece quando você faz isso?
O que eu queria dizer no twitter é que a conversão pode ser tentada pelo otimizador antes que as linhas sejam eliminadas, então você não pode considerar apenas os valores CompletionDate que são retornados pela junção.
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.