SELECT Statement - Date Criteria

Question

I have a line of code

string datenow = DateTime.Now.ToString("dd'/'MM'/'yyyy");
"SELECT * from trialevent where date <= '"+datenow+"'";

For example, datenow is 12/02/2016

"SELECT * from trialevent where date <= '12/02/2016'";

which means I want data where date after or equal to 12/02/2016, but i keep getting this

trialevent | date
38         | 10/02/2016
39         | 11/02/2016
40         | 11/02/2016
41         | 12/02/2016

Here's the thing, I don't want 10/02/2016 and 11/02/2016. And I have a column 24/02/2016 which is not showing.

What datatype is your `date` column? What DBMS are you using? (It matters; there are syntax differences.) And what language are you using for your code? If it's a language that supports them, and the column is actually of type DATE or DATETIME, you need to immediately stop concatenating your SQL and start using parameterized queries, in which case you can also stop using ToString() and let the database driver do the proper conversion. — Ken White, Feb 12 '16 at 02:53
Why? If it's a DATE, why would you use VARCHAR? That's exactly why your WHERE isn't working; in a string sort, `24/02/2016` is > than `12/02/2016` (because `24` is greater than `12`), and therefore it **should** be in the matches you get. If you used proper data types, you can find what you need when you need it. — Ken White, Feb 12 '16 at 02:59
Also, you should not insert values into your SQL statements as strings. See the answers to this question for details: http://stackoverflow.com/questions/9516625/prevent-sql-injection-attacks-in-a-java-program — Turophile, Feb 12 '16 at 04:07

score 3 · Accepted Answer · answered Feb 12 '16 at 02:51

3

Use proper ISO standard dates formats: YYYY-MM-DD.

Or, just use the database CURDATE():

SELECT *
from trialevent
where date <= CURDATE()";

answered Feb 12 '16 at 02:51

Gordon Linoff

2

Yeah that or simply don't store datetimes as strings in your database if possible (which is more than likely the *real* answer here). – lc. Feb 12 '16 at 02:53
1

@Joey: You're confusing **presentation** (what is more commonly seen in your country) and **data** (which is the internal way it is stored). They're not the same thing, and you shouldn't treat them as if they are; there are specific data types for various things for a good reason, not just because someone thought it would be fun to have them around. – Ken White Feb 12 '16 at 03:02

1 Answers1