Query returning error in query between date period

Question

Consultation:

SELECT 
    ROW_NUMBER() OVER(ORDER BY V.DATA ASC) AS ID,
    V.CHAPA AS CHAPA,
    F.NOME  AS NOME,
    V.DATA  AS DATA,
    CASE WHEN V.BATIDA IS NULL THEN 0 ELSE V.BATIDA END AS FOLGA
        FROM
            ARELBATIDATRANSITOVIEW AS V
            LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
      WHERE
          V.CHAPA = 123 
           AND V.DATA BETWEEN '2016-04-01 00:00:00.000' AND '2016-09-30 00:00:00.000'
            GROUP BY V.CHAPA,V.DATA,F.NOME,V.BATIDA
            ORDER BY DATA ASC

Is returning me the following message:

Message 242, Level 16, State 3, Line 27 The Conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

SQL SERVER 2008

Answer 1

If the field V.DATA be the type DATETIME you can use the CONVERT and specify the type of model string will be. I’d be more or less like this.

SELECT * FROM #TMP_B
WHERE DATA BETWEEN CONVERT(DATETIME,'2016-10-19',120) AND CONVERT(DATETIME,'2016-10-21',120)

If you want to see the conversion models, you can look here: http://www.w3schools.com/sql/func_convert.asp

or here: https://msdn.microsoft.com/pt-br/library/ms187928.aspx

Answer 2

Octavio, the error message indicates line 27 but in the code you transcribed there are 14 lines...

If column V.DATA is declared as datetime, it seems to me that the cause is related to the session LANGUAGE. One solution, which works independently of how LANGUAGE and/or DATEFORMAT is configured, is to use the Convert() function. It cannot be the Cast() function because it is context sensitive.

And, to facilitate code maintenance, I suggest you create variables to store the values of the period to be consulted.

-- código #1
declare @DataInicial datetime, @DataFinal datetime;

-- informe aqui as datas limite, no formato dd/mm/aaaa
set @DataInicial= Convert(datetime, '1/4/2016', 103);
set @DataFinal= Convert(datetime, '30/9/2016', 103);

--
SELECT 
    ROW_NUMBER() OVER(ORDER BY V.DATA ASC) AS ID,
    V.CHAPA AS CHAPA,
    F.NOME  AS NOME,
    V.DATA  AS DATA,
    CASE WHEN V.BATIDA IS NULL THEN 0 ELSE V.BATIDA END AS FOLGA
  FROM
            ARELBATIDATRANSITOVIEW AS V
            LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
  WHERE
          V.CHAPA = 123 
           AND V.DATA BETWEEN @DataInicial and @DataFinal
  GROUP BY V.CHAPA,V.DATA,F.NOME,V.BATIDA;

Code #1 used the 103 style in the Convert() function, which means that the date is given in the dd/mm/yyyy format. This makes it easier to maintain the code, as it is our day-to-day format. Since the variables @Initial and @Datafinal are declared as datetime, comparisons are all performed as values datetime.

---

There are factors that can confuse when manipulating dates in SQL Server, leading to errors in execution almost always related to the ignorance of how SQL Server interprets literals (strings) containing date. For example, "2020-02-29 18:12:58" is correctly recognized as 29/2/2020 18:12:58 if the data type is datetime2 but can generate error message 242 if the data type is datetime and dateformat is not set to ymd. In the article Dominating dates and times in SQL Server this subject is dealt with in depth.