Skip to main content

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

28
  • 8
    You'll see better performance if you change UNION to UNION ALL - it's wasting time checking for duplicates to remove that don't exist. It's overcomplicated IMO though - if you're going to construct a resultset using UNIONs, why not just specify the date and be done with it? Commented Jan 28, 2010 at 21:27
  • 8
    why not just specify the date and be done with it - because the above method allows you to create arbitrarily large sets of numbers (and dates) requiring no table creation, that would be painful to hard-code in the manner you are suggesting. Obviously for 5 dates it is overkill; but even then, if you are joining against a table where you do not know the dates in advance, but just the potential min and max values, it makes sense. Commented Jan 28, 2010 at 22:10
  • 2
    It's "painful" to just use the DATETIME function in place of the UNION statement you've already created? It alleviates any need for the logic you had to add. Hence - you've overcomplicated the query. The UNION statement, either way, is not scalable - specifying a date or number, who wants to update it to accommodate say 20 or 30 dates? Commented Jan 28, 2010 at 22:36
  • 34
    It's really nice to see an answer to the question, not endless comments how it cannot, or should not, be done. Most things can be done, and "should" is only meaningful in context, which differs for everyone. This answer helped me, even though I am well aware there are better ways in most situations. Commented May 30, 2012 at 0:58
  • 8
    Those of you who cannot get this query to work: Please slap yourself in the face and then re-read the OP's comment about this query generating 1000 dates. Since 2010 was more than 1000 days ago, you'll need to adjust the query accordingly. Commented Feb 8, 2015 at 9:30