6

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)
Kin Shah
  • 612
  • 6
  • 25
R.salih
  • 135
  • 8

3 Answers3

3

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!

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • 1
    it gives me this error Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string. – R.salih Apr 30 '15 at 15:39
  • 1
    Comparing Strings will use alphabetical order char by char starting from the left. For example: 20/01/1302 will be after 01/12/4016 as the "2" char goes after the "1" char. – borjab Apr 30 '15 at 15:40
  • 1
    @Giorgi Nakeuri but in the database its not organized this way(20150430) its organized(30/04/2015) – R.salih Apr 30 '15 at 15:46
  • 1
    @R.salih, see edited answer. You should convert with appropriate format in order to eliminate convertion error – Giorgi Nakeuri Apr 30 '15 at 15:47
  • 1
    @GiorgiNakeuri the datatype of the RegisterdDate is nvarchar – R.salih Apr 30 '15 at 15:52
  • 1
    @R.salih, I understand. My select will select you the data you want. But for storing dates you should use Date type not nvarchar – Giorgi Nakeuri Apr 30 '15 at 15:53
  • 1
    If you still get a conversion error then you should check for rows that can't be converted. For instance if the registered date is 'zzzzzzzzz'. In a separate window run this `SET DATEFORMAT dmy SELECT * FROM Student WHERE ISDATE(Student.RegisteredDate) = 0` This will return rows where the date couldn't be converted. You'll have to manually fix these. – SQLChao Apr 30 '15 at 16:09
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

r.hamd
  • 264
  • 1
  • 4
  • 18
1

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.

borjab
  • 11,149
  • 6
  • 71
  • 98
  • Could you kindly tell me what is ( to_Date)? – R.salih Apr 30 '15 at 15:50
  • 1
    '30/01/2014' is just a String type like 'Helloworld' and 'FooBar'. They do not have any date meaning. Date types are optimized for database and make some checks so you wont find '31/02/2015' or '33/13'1233'. To_date will just change the type See for example: http://www.connectsql.com/2011/04/sql-server-basics-todate-function-in.html – borjab Apr 30 '15 at 15:54
  • @borjab, isnt it just for oracl? – r.hamd Apr 30 '15 at 15:59
  • 3
    to_date doesn't exist in SQL 2014. – SQLChao Apr 30 '15 at 15:59
  • 4
    `To_date is removed in SQL Server 2014 ` - it was never in sql server – Giorgi Nakeuri Apr 30 '15 at 16:13