Take the 2-minute tour ×

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.

share|improve this question
Why can you not do CASE? –  JNK Jan 23 '12 at 21:04
because i don't have the possibilities for leap-year, bussinnes day, not holiday, etc. –  Leandro Jan 23 '12 at 21:08
add comment

2 Answers

up vote 1 down vote accepted

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


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

share|improve this answer
This can have performance issues as the entire output of the function has to be processed for a join. Query plans involving tvfs can be quite inefficient. –  ConcernedOfTunbridgeWells Jan 25 '12 at 9:53
add comment

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.

share|improve this answer
add comment

Your Answer


