Hello,
Something like this should work
where convert(varchar(10),EffDt,101) >= '06/01/2020'
and convert(varchar(10),EffDt,101) <= '06/30/2020'
I confirm, the 101 is the Convert format for MM/DD/YYYY.
However, consider these results:
select count(*)
from statement
where convert(varchar(10), stmnt_creation_date, 101)
between '12/01/2006' and '12/31/2006'
-----------
306
Note that even though the date range was for 2006 it pulled in some 2003 records. Weird.
select count(*)
from statement
where stmnt_creation_date >= convert(datetime, '12/01/2006 00:00:00')
and stmnt_creation_date <= convert(datetime, '12/31/2006 23:59:59')
-----------
282
Note that by using specific time values it got the right answer of 282 records.
select convert(varchar(10), stmnt_creation_date, 101)
from statement
where convert(varchar(10), stmnt_creation_date, 101)
between '12/01/2006' and '12/31/2006'
and datepart(year,stmnt_creation_date) = 2006
order by stmnt_creation_date asc
It also got the right answer when the datepart function limited the return to 2006 data.
So there's some thing in the conversion process that's not quite right.
I'd switch from the between logic to the date with time limits.
Regards,
Terry