Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

2 Answers 2

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:

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.

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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