Conversion of type VARCHAR to SMALLDATETIME

Question

Conversion of type VARCHAR to SMALLDATETIME

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

    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 after adding 18 hours (64800 seconds). For example, for the current date 12/27/2016 06:00:00: Subtract 1 day: 12/26/2016 06:00:00 Matches the result to: 26/12/2016 00:00:00 Add 64800 seconds (18hrs): 12/26/2016 6:00 PM

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

sql-server

asked by anonymous 27.12.2016 / 16:39

2 answers

Send form to controller with ajax Identify the status of a program with java

score 2 · Answer 1

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

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

You can identify the bank language in the properties, or by using the command select @@language

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

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

score 0 · Answer 2

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

A = Cast (Current_Timestamp as date)

"subtract one day"
B = DateAdd (day, -1, A)

C = DateAdd (hour, +18, Cast (B as datetime))

Putting it all together, we have:
DateAdd (hour, +18, Cast (DateAdd (day, -1, Cast (Current_Timestamp as date)) as datetime))

I think the WHERE clause looks like this:

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

Even better if you mount 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 cdtHrProcesso column is of type datetime, then the variable @Ontem18h was declared in the same type, thus avoiding implicit conversion.