• 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