How to convert date time format in SQL Server like ‘2017-03-04 10:07:03.490’ to date format which is seperated by – like ‘2-11-2016’

I am tying to make date comparison with the query

select * 
from dbo.OH_Case   
where dbo.OH_Case.CreatedDate between Convert(varchar(30),'24/04/2017', 102)  
                                  and Convert(varchar(30),'01/05/2017', 102) 

but it throws this error:

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.

My application passes data in ’01/05/2017′ format and the date information in SQL Server is stored in ‘2017-03-04 10:07:03.490’ format. What should I do to make both of these in same format which allows comparison?

Answer

First, you’re converting a string literal to a varchar. Convert it to a datetime instead.

Second, you’re passing in 24/04/2017, but date format 102 is yyyy.mm.dd. Try date format 103 for dd/mm/yyyy:

convert(datetime, '24/04/2017', 103)