Conversion from VARCHAR to SMALLDATETIME

Question

    SELECT DISTINCT p.codInterno,
                CASE
                    WHEN f.idFabricante IS NULL THEN 99999999999999
                    ELSE f.idFabricante
                END AS fabri,
                '0', 
                p.descResumida, 
                p.descProduto,
                p.descProduto,
                p.codElemento,
                GETDATE(),
                GETDATE(),
                0,
                0  
    FROM    [HORPDB00\PDBSIAC].[SiacPRD].[dbo].[IntPluProduto] p
        INNER JOIN  [HORPDB00\PDBSIAC].[SiacPRD].[dbo].[IntControle] c on c.idProcesso = p.idProcesso
        LEFT JOIN [HORPDB00\VANR].[tlmark].[dbo].[Produto] vanProduto on vanProduto.CdPrdSAP = p.codInterno
        LEFT JOIN tbFabricante f on f.idFabricante = CAST(vanProduto.CdFbr AS int)
    WHERE  NOT EXISTS(select 1 from tbProduto where idSAP = p.codInterno)
    AND vanProduto.cdFbr <> 'NNN0'
    AND CAST(c.dtHrProcesso AS DATE) > DATEADD(ss, 64800, DATEADD(d, -1, CAST(CONVERT(varchar(10), GETDATE(), 103) AS smalldatetime))) 
    OR CAST(c.dtHrProcesso AS DATE) = CAST(GETDATE() AS DATE)
    AND c.codModulo = 14
    AND vanProduto.ICMSubst <> 'T'

At this point:

AND CAST(c.dtHrProcesso AS DATE) > DATEADD(ss, 64800, DATEADD(d, -1, CAST(CONVERT(varchar(10), GETDATE(), 103) AS smalldatetime))) 
    OR CAST(c.dtHrProcesso AS DATE) = CAST(GETDATE() AS DATE)

The above filter does nothing but, starting from the current date, subtracts 1 day, and then adds 18 hours (64800 seconds). For example, for the current date 27/12/2016 06:00:00: Subtract 1 day: 26/12/2016 06:00:00 Format result to: 26/12/2016 00:00:00 Add 64800 seconds (18hrs): 26/12/2016 18:00:00

Conversion error: The Conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

Answer 1

Your problem must be in date format, which is probably different from the database.

The command CONVERT(varchar(10), GETDATE(), 103) returns the date in dd/mm/yyyy format, because of the "103" format, but if your seat is set to US for example will not work. The problem actually happens in CAST, not in the CONVERT.

You can identify the language of the database in the properties, or using the command select @@language

I did this test here in SQL 2012 and the same error happened. Replacing the format with "101" (my SQL is configured in us_english), worked perfectly:

select CAST(CONVERT(varchar(10), GETDATE(), 101) AS smalldatetime)

Answer 2

The above filter does nothing but, starting from the current date, subtract 1 day, and after add 18 hours

"from the current date"
To= Cast (Current_timestamp as date)

"subtract a day"
B= Dateadd (day, -1, A)

"adds 18 hours"
C= Dateadd (hour, +18, Cast(B as datetime))

Put it all together, we got:
Dateadd(hour, +18, Cast( Dateadd(day, -1, Cast(Current_timestamp as date)) as datetime) )

It seems to me that the WHERE clause would look like this:

WHERE c.dtHrProcesso > DateAdd(hour, 
                               +18,
                               Cast( DateAdd(day, -1, Cast(Current_Timestamp as date)) as datetime) )

---

Even better to assemble something similar to

-- código #1
declare @Ontem18h datetime;
set @Ontem18h= DateAdd(hour, 
                       +18, 
                       Cast( DateAdd(day, -1, Cast(Current_Timestamp as date)) as datetime) );

SELECT ...
  from ...
  where c.dtHrProcesso > @Ontem18h;

Note that since the cdtHrProcess column is type datetime, then the @Ontem18h variable was declared in the same type, thus avoiding implicit conversion.