Why my selection between two dates in the same table doesn't work?

Question

I use SQL server 2014, i try the following query to select between two dates in the same table, the datatype is nvarchar, i executed the following query it just shows me three rows such('30/03/2015','30/04/2015','30/04/2015'),but in reality there is('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

Cast the other way round, other way you are comparing strings:

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

The fact is that those 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 would you 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 you those three rows. Also you have in your data 29/02/2015. In 2015 February ends on 28(you have incorrect data in tables already). You will never be able to insert such values if you choose types correctly.So the conclusion is:

Use Appropriate data types for your data!

Answer 2

As i have read the other answers and comments, i could recommend you to firstly change the datatype of "RegisteredDate" from "nvarchar" to "date". Secondly use this standard 'yyyy-MM-dd' below code is what you need

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

you will not be in need of any conversions, this is how i do it for myself

Answer 3

Try to cast your string to date instead of casting all the dates in the table to string.

You are casting all the records in the table to string and it might be millions of them. This way not only your performance will be better but more important you will compare them to dates, not as 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, a string comparation uses alphabetical order one char after another starting from the left. For example: 20/01/1302 will be after 01/12/4016 as the "2" char goes after the "1" char in ASCII.

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

SQL Server 2014 makes the string to dates conversion automagically but only if it needs to. Comparing a '30/04/2015' string with a nvarchar is just a String comparation.