Member Avatar for amorphis88 
"SELECT * FROM tblKalender order by datum asc where datum > '" + Format(System.DateTime.Now, "MM-dd-yy") + "'"
  • 3 Contributors
  • 4 Replies
  • 93 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by chithrasujith

Dani AI

Generated

Two separate issues are colliding here: the SQL clause order and comparing dates as text. correctly pointed out that the WHERE clause should come before ORDER BY. Also note that 's GETDATE() is SQL Server-specific and 's sysdate is Oracle-specific. For a MySQL back end (this thread is tagged mysql) the safer, simpler options are MySQL date functions or passing a DateTime parameter from ASP.NET instead of formatting a date string on the client and concatenating it into SQL.

If datum is a DATE or DATETIME use the server functions so comparisons are numeric/date-based rather than string-based. For example, to exclude past dates use CURDATE() (date only) or NOW() (date+time):

SELECT * FROM tblKalender
WHERE datum > CURDATE()
ORDER BY datum ASC;

If datum is stored as text, comparisons will be wrong unless you convert it. Prefer changing the column to DATE/DATETIME. If you cannot change the schema immediately, parse the text in the query (MySQL example):

WHERE STR_TO_DATE(datum, '%m-%d-%y') > CURDATE()

From ASP.NET the best practice is parameterized queries to avoid format and injection problems. Example (MySqlConnector / Connector/NET):

using (var cmd = new MySqlCommand("SELECT * FROM tblKalender WHERE datum > @dt ORDER BY datum", conn))
{
    cmd.Parameters.AddWithValue("@dt", DateTime.Now);
    ...
}

Troubleshooting checklist: confirm datum column type (DESCRIBE tblKalender), check whether timezones or time parts matter (use NOW() vs CURDATE()), and convert the column to DATE/DATETIME when possible. This will prevent the event like "TEST" on Aug 2 from appearing after the current date.

Recommended Answers

All 4 Replies

Member Avatar for DB_err

What type of error message are you getting?

HEre are two things I see at first glance.

1.
the "order by" section should come after the "where statement"

"SELECT * FROM tblKalender where datum > '" + Format(System.DateTime.Now, "MM-dd-yy") + "' order by datum asc"

2.
The code is comparing the field "datum" with a text string. Is the "datum" field a text string?

Member Avatar for amorphis88

the query is ok but it doesn't do what i want..

the event "TEST" on the second of august shouldn't be displayed because the date is BEFORE systemdate

Member Avatar for DB_err

What dbms are you using? If this were Oracle for example, and the datum field was configured as a date field, the following should work.

SELECT * FROM tblKalender where datum > sysdate order by datum asc
Member Avatar for chithrasujith

Try this

SELECT EventDate, Activity FROM Dani_Table  WHERE (EventDate > GETDATE()) order by EventDate asc
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.