Skip to content
Advertisement

SQL between date range returning a wrong result

I’m writing a SQL query to get rows between a date range format: mm/dd/yyyy

I used the between operator but the result shows out of the range dates

Select Internship_StartDate1
From [Media_BPM].[dbo].[BP_UGME]
Where TrainingType = 'Medical Internship Training'
  and Internship_StartDate1 Between '01/%/2020%'  AND '06/%/2020%'
Order By Internship_StartDate1 desc

The expected result : dates of year 2020 only

The actual result is:

Internship_StartDate1
05/16/2018
05/06/2018
05/05/2019
05/05/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/02/2019
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2020
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2019
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2018
05/01/2017
04/30/2018
04/29/2018
04/18/2018
04/14/2017
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2020
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2019
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2018
04/01/2017
04/01/2017
04/01/2017
04/01/2017
03/19/2019
03/18/2018
03/04/2018
03/03/2019
03/03/2019
03/03/2019
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2020
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2019
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2018
03/01/2017
03/01/2017
03/01/2017
03/01/2017
03/01/2017
03/01/2017
03/01/2017
03/01/2017
03/01/2017
03/01/2017
03/01/2017
03/01/2017
03/01/2017
03/01/2017
03/01/2017
03/01/2017
03/01/2017
02/05/2017
02/03/2019
02/03/2019
02/03/2019
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2020
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2019
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2018
02/01/2017
02/01/2017
02/01/2017
02/01/2017
02/01/2017
02/01/2017
02/01/2017
02/01/2017
02/01/2017
02/01/2017
02/01/2017
02/01/2017
02/01/2017
01/20/2019
01/19/2019
01/19/2019
01/13/2019
01/12/2019
01/12/2019
01/12/2019
01/10/2019
01/10/2019
01/10/2018
01/09/2019
01/09/2019
01/09/2017
01/08/2018
01/02/2019
01/02/2019
01/02/2019
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2020
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2018
01/01/2017
01/01/2017
01/01/2017
01/01/2017
01/01/2009

Advertisement

Answer

I assume that you use SQL Server.
Maybe you think that % is a wildcard char in this condition denoting any char, but this is not the case.
You can use % with the operator LIKE but not with BETWEEN.
Use the function FORMAT():

Select Internship_StartDate1 
from [Media_BPM].[dbo].[BP_UGME] 
where TrainingType = 'Medical Internship Training' 
AND FORMAT(Internship_StartDate1, 'yyyyMM') Between '202001' AND '202006' 
Order By Internship_StartDate1 desc
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement