CODEWITHSUNDEEP
sqlvb.netfiltering
GertDeWilde
GertDeWilde

Reputation: 351

SQL Filter doesn't work with past year

I got a very strange problem.

When I filter my data in the current year everything is working fine: enter image description here But If I try to go back a year, suddenly I get a blank report... enter image description here

Going to the next year isn't a problem, like you can see here: enter image description here

My code is:

These 2 values come through as a 'date' in a private sub
datumvan = DateTimePickerVan.Value.ToLocalTime
datumtot = DateTimePickerTot.Value.ToLocalTime


Dim culture As New CultureInfo("pt-BR")
sqlstr = "SELECT * FROM [Geschiedenis$] WHERE Aangemeld BETWEEN '" & datumvan.ToString("d", culture) & "' AND '" & datumtot.ToString("d", culture) & "'"

See also how the sql looks like if it goes to the debugging: enter image description here

So to conclude: everything is working fine unless I go to the previous year. If I place: NOT BETWEEN, everything is reversed, and I see all the data.

I tried to replace BETWEEN with >= and <= as well, but the same thing occurred.

Any ideas?

Upvotes: 0

Views: 330

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Aangemeld is obviously a date, as it should be. So you must compare Aangemeld with dates. In order to do so,

  • first convert the dates to strings in VB. Make this a specific date format, rather than relying on some region settings. E.g. datumvan.ToString("dd/MM/yyyy")
  • then convert the string to date in SQL. Again don't rely on database settings, but name the format you are using.

How to convert a string to a date in SQL depends on the DBMS you are using. Here are some examples:

MySQL:

" ... WHERE Aangemeld BETWEEN STR_TO_DATE('" & datumvan.ToString("dd/MM/yyyy") & "','%d/%m/%Y') AND ... "

SQL_Server:

" ... WHERE Aangemeld BETWEEN CONVERT(datetime,'" & datumvan.ToString("dd/MM/yyyy") & "',103) AND ... "

Oracle:

" ... WHERE Aangemeld BETWEEN TO_DATE('" & datumvan.ToString("dd/MM/yyyy") & "','dd/mm/yyyy') AND ... "

Many DBMS (such as MySQL, SQL-Server, and PostgreSQL) also accept a string in ISO format as as date literal.

" ... WHERE Aangemeld BETWEEN '" & datumvan.ToString("yyyyMMdd") & "' AND ... "

In Oracle such a literal must be preceded by DATE:

" ... WHERE Aangemeld BETWEEN DATE'" & datumvan.ToString("yyyyMMdd") & "' AND ... "

One more thing: I don't know if this is the case in other DBMS, too, but I know that Oracle treats a date without time as a date at midnight, as it only knows datetime data type. So a between clause would exclude the last day. Which is why in Oracle you would compare TRUNC(Aangemeld) instead of Aangemeld.

Upvotes: 1

Related Questions