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?
Advertisement
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)