StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>My preferred solution is to have an extra column that you can index; <code>next_birthday</code>.</p> <p>As an overnight process you update that column to ensure the value is always in the future.</p> <p>This allows queries such as the following:</p> <pre><code>SELECT myfriends.* FROM user AS myself INNER JOIN user AS myfriends ON myfriends.next_birthday >= myself.next_birthday AND myfriends.next_birthday < DATEADD(MONTH, 1, myself.next_birthday) </code></pre> <p>This keeps the <code>year</code> part of the datetime field, and so cope with <code>going around the corner</code> very well:<br> - If my next birthday is <code>2012-12-15</code><br> - I want birthdays in the last half of Dec <em>and</em> birthdays in the first half of Jan<br> - Solutions that use Date and Month only <em>(not year)</em> struggle with this </p> <p>The field can be indexed and so the solution does not <em>require</em> a scan of every record in your table.</p> <p>It does, however, assume that you can run that maintenance job to update the field. Where the job has not executed, a portion of the data will be out of date.</p> <p>You also need to be careful of people with a birthday on <code>29th Feb</code> - you can't just keep adding one year to their birthday; <code>29th Feb + 1 year</code> moves to <code>28th feb</code>, and no matter how many years you add, you never get back to <code>29th Feb</code>. But that's not too hard...</p> <pre><code>UPDATE user SET next_birthday = DATEADD(YEAR, DATEDIFF(YEAR, birthday, GETDATE()) + 1, birthday) WHERE next_birthday < GETDATE() </code></pre> <p>All of this is also <em>very</em> narrow in scope. It introduces a new column and a new maintenance job, for one very particular need. The question should then be asked; Are those costs preferable to the alternative queries <em>(which often require table scans, etc)</em>?</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