MOM and EOM in SQL Server 2005

Question

What is the most sample and practical way to know the MOM (middle of month) and EOM (end of month) in an store procedure on SQL Server 2005? (without doing a select case / switch)

EOM is the last day of the month, and MOM is the last day of the fortnight. It's seems to be 15 in every month except on february.

Also I need to take care the leap-year, Holidays, businness day option, etc. Like linux.

Answer 1

For complex date and time functions, use a user defined table-valued function and filter the results from it:

http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

There are a number of ways to do this, but this is one I prefer.

Answer 2

In a data warehouse with a date dimension it's often common practice to annotate the dimension with this sort of thing. You can use a TVF to generate the initial data but perisisting it in a table will be faster for most purposes. There's no particular reason that this technique could not be applied to a transactional application.