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 Jan 23 '12 at 21:04
  • because i don't have the possibilities for leap-year, bussinnes day, not holiday, etc. – MacGyver Jan 23 '12 at 21:08

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. – ConcernedOfTunbridgeWells Jan 25 '12 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, privacy policy and cookie policy

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