MOM and EOM in SQL Server 2005

Posted on

Question :

What is the most sample and practical way to know the MOM (middle of month) and EOM (end of month) in a stored procedure in 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 seems to be 15 for every month except on February.

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

Answer :

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.

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.

Leave a Reply

Your email address will not be published.