StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you have control over the database but MUST use a text-based date, store your dates using the ODBC canonical format:</p> <pre><code>yyyy-mm-dd // if there's no time element yyyy-mm-dd HH:MM:ss // if time is needed as well </code></pre> <p>This has a few distinct advantages:</p> <ul> <li>World-friendly, for users who aren't in the US and may think mm-dd-yyyy means dd-mm-yyyy</li> <li>Sorts by date naturally, so normal < and > operators work just fine (and those operations are doing a textual comparison, they never actually convert the text to a date).</li> <li>Your business layer will likely be able to read dates in this format correctly without adjusting your code at all</li> <li>If you have fields that don't have actual dates, this won't generate a CONVERT() error like many of the suggestions already posted. (For instance, if you are also dealing with dirty values like "Next Tuesday" or "N/A" that you can't clean out of the database.)</li> </ul> <p>Converting your existing date data is a simple exercise of UPDATE with RIGHT(), LEFT(), etc., assuming your current date data is in a consistent format.</p> <p>Once your data is stored in a format that can be queried more readily, it's a simple problem:</p> <pre><code>SELECT TOP 6 * FROM mytable WHERE mydate BETWEEN startdate AND enddate ORDER BY mydate DESC </code></pre> <p>As for your grouping problem, I don't understand the question well enough to propose an answer. But getting your date data stored in the most efficient text format will help sort everything else out.</p> <p>Ok, I'm going to take a stab at your grouping problem:</p> <pre><code>SELECT DISTINCT DueDate, DocType, PayTo, ContactName, ContactNumber, [...other fields...] FROM coupons c1 WHERE CDate(c1.DueDate) BETWEEN '01/01/2000' AND '01/01/2009' /* Here's where the "grouping" happens--actually just filtering out the others */ AND (SELECT COUNT(*) FROM coupons c2 WHERE CDATE(c1.DueDate) >= CDATE(c2.DueDate) AND c2.DocType=c1.DocType AND c2.ContactName=c1.ContactName AND c2.ContactNumber=c1.ContactNumber [...test the other fields...] ) <= 6 </code></pre> <p>I can't remember the ins and outs of Jet SQL enough to know if this subquery will work, but I think it will.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload