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.

  • Why can you not do CASE?
    – JNK
    Commented Jan 23, 2012 at 21:04
  • because i don't have the possibilities for leap-year, bussinnes day, not holiday, etc. Commented Jan 23, 2012 at 21:08

2 Answers 2


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.

  • 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. Commented Jan 25, 2012 at 9:53

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.