Why is my choice between two dates in the same table not working?

Question

I am using SQL Server 2014, I am trying to execute the following query to select between two dates in the same table, nvarchar data type, I executed the following query, it just shows me three rows like ('30/03/2015','30/04/2015','30/04/2015') ('29/02/2015','30/03/2015','31/04/2015','30/04/2015','30/04/2015') ('30/03/2015','30/04/2015','30/04/2015') , but actually exists ('29/02/2015','30/03/2015','31/04/2015','30/04/2015','30/04/2015')

 select RegisteredDate from Student where Student.RegisteredDate between convert(nvarchar, '30/01/2014', 103) and convert(nvarchar, '30/04/2015', 103) 

Answer 1

As I read other answers and comments, I could recommend that you first change the "RegisteredDate" data type from "nvarchar" to "date". Secondly, use this standard "yyyy-MM-dd" below the code that you need

 select RegisteredDate from Student where Student.RegisteredDate between '2014-01-30' and '2015-04-30' 

you don’t need any transformations, this is how I do it for myself

Answer 2

Point it the other way, otherwise you are comparing strings:

 select RegisteredDate from Student where convert(date, Student.RegisteredDate, 103) between '20140130' and '20150430' 

The fact is that these dates, saved as strings, are ordered as:

 '29/02/2015', '30/03/2015', '30/04/2015', '30/04/2015', '31/04/2015' 

Now imagine where you would add filter values?

  '29/02/2015', '30/01/2014' --start date /-------------\ |'30/03/2015',| |'30/04/2015',| |'30/04/2015',| \-------------/ '30/04/2015' --end date '31/04/2015' 

So, between will return these three lines to you. Also you have data 29/02/2015 . In 2015 February ends at 28 (you already have invalid data in the tables). You can never insert such values ​​if you select the types correctly. In this way:

Use the appropriate data types for your data.

Answer 3

Try applying your row to the date instead of inserting all the dates in the table into the row.

You produce all the entries in the table in a row, and it can be millions. Thus, not only your performance will be better, but also more important, you will compare them with dates, not with a string.

 SELECT RegisteredDate FROM Student WHERE Student.RegisteredDate BETWEEN Convert(Date, '30/01/2014',103) AND Convert(Date, '30/04/2015', 103) 

As I said in the comments, string comparisons use one char after another in alphabetical order, starting from the left. For example: 20/01/1302 will be after 01/12/4016, since the "2" char comes after the "1" char in ASCII.

Update : convert Student.RegisteredDate to date if it is still in type nvarchar. I would recommend you change the type if possible. This can be a source of errors and performance problems if you do not.

SQL Server 2014 automatically converts a string to dates, but only if necessary. Comparing the string "04/30/2015" with nvarchar is just a string comparison.