I always used to handle the EndOfLastMonth problem by stripping the Month and Year out of the date as strings and using them to build the FirstOfNextMonth date, then using DATEADD to move back a day. It always felt a little clunky (and all the CASTs made the code less readable), so I like the much cleaner method proposed by Lynn (which is similar to what ps posted, as well).

However, I have one question. This method is contingent on the ability to cast the value 0 as a datetime = 01/01/1900, and -1 as 12/31/1899. Is this a stable standard (pardon the alliteration)? In other words, is it possible that MS could change something in the way they process dates that would cause this to break? I imagine using 0 and -1 would be safer than using 01/01/1900 and 12/31/1899, as 0 would represent Day 0, no matter what day that is. But I just wanted to check that this sort of casting is fairly conventional.

Just curious (since I'm planning on refactoring my FirstOfMonth and LastOfMonth functions to adopt this new method).

By the way, this also made me realize that I could strip the timestamp from any datetime by using this: dateadd(dd, datediff(dd, 0, @ThisDate), 0)

Again, this is much more streamlined than my string manipulation method.

So thanks for showing me the light.