Conversion of a data type scan into a data type datetime resulted in a value outside the yyyy-mm-dd range

Question

I have a column called produtodata which has the following datetime format:

1753-01-01 00:00:00.000

I need to filter the records that are between the date '2019-06-26 00:00:00.000' until today.

I tried something like:

SELECT produtodata, * FROM PRODUTO WHERE PRODUTODATA BETWEEN CAST('2019-06-26 00:00:00.000' AS DATETIME) AND CAST('2019-09-09 00:00:00.000' AS DATETIME)

But I get:

Conversion of a data type scan into a data type datetime resulted in a value outside the range.

I also tried without CAST, but I get the same error.

Answer 1

If such error message is being issued it is because the PRODUCTODATA column is declared as text (varchar(n), for example). In this case it is necessary to know which format the date is stored in, as it can be "yyyy-mm-dd", "dd/mm/yyyy", "mm/dd/yyyy" among several available formats. From the excerpt "1753-01-01 00:00:00.000" it seems to me that it is "yyyy-mm-dd hh:mm:ss.mmm".

-- código #1
declare @DataInicial datetime, @DataFinal datetime;
set @DataInicial= convert (datetime, '26/6/2019', 103);   -- dd/mm/aaaa
set @DataFinal= convert (datetime, '9/9/2019', 103);      -- dd/mm/aaaa

SELECT produtodata, * 
  from PRODUTO 
  where convert (datetime, PRODUTODATA, 121) between @DataInicial and @DataFinal;

The code above is non sargable.

Answer 2

As my sql was in English, it was necessary to run this command:

set dateformat ymd

After that the consultation worked.