Coversion date failed

1

Hi All.
I tried to execute very simple select but get error:
"Conversion failed when converting date and/or time from character string."

declare @DateFrom as Date
declare @DateTo as Date
set @DateFrom = '2016/12/12'
set @DateTo = '2016/15/12'

select convert(varchar(10), TestDate,111)
from Table1
Where convert(varchar(10),TestDate,111) between convert(varchar(10),@DateFrom,111) and convert(varchar(10),@DateTo,111)

Where I wrong and how to fix it?

Thanks.

2

Don't use date formats that rely on local date settings, such as yyyy/dd/mm.
Instead use yyyymmdd, which always works, regardless of any/all date and/or language settings.

Also, for efficiency and accuracy, don't use between on date/datetime comparisons, use >= and < instead:

declare @DateFrom as Date
declare @DateTo as Date
set @DateFrom = '20161212'
set @DateTo = '20161215'

select convert(varchar(10), TestDate,111)
from Table1
Where TestDate >= @DateFrom and TestDate < DATEADD(DAY, 1, @DateTo)

3

do not use the string value of a date to do a compare. Even though the compare is yyyy/mm/dd it is better just to use the dates themselves.

4

There are many way to do this:
https://msdn.microsoft.com/en-us/library/ms187928.aspx.
these formats are dependent on what settings you have - therefore, sometimes these may not work

5

111 format is YYYY/MM/DD, your statement, "set @DateTo = '2016/15/12'" is trying to assign MM 15 which is not valid.