SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Question

I have been getting the following error when running a SQL to convert my data type value from varchar to datetime.

Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I have checked the data and can't see anything to add as I Ran the following checks and all are returning no results:-

SELECT [Date] from table where [DATe] is null 
SELECT [Date] from table where [DATe] = '' 
SELECT [Date] from table where LEN([date])> 10 
SELECT [Date] from table where LEN([date])< 10 
SELECT top 100 [Date] , SUBSTRING([date],4,2) from [table where convert(int,SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 12 
SELECT top 100 [Date] , SUBSTRING([date],1,2) from table where convert(int, SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 31

Is there anything else worth looking at or helping with this issue? Cannot seem to find a way.

Answer 1

 I ran into the same problem due to a very minimal and careless mistake. Make sure the date actually exists

For example:

September 31, 2015 does not exist.
 

EXEC dbo.SearchByDateRange @Start = '20150901' , @End = '20150931'

So this fails with the message:
 

Error converting data type varchar to datetime.

To fix it, input a valid date:
 

EXEC dbo.SearchByDateRange @Start = '20150901' , @End = '20150930'

This will ensure it works just about fine.