Saturday, July 30, 2011

Function for End of Month

Did you have a requirement  of getting last date of the month by passing any date. I can remember I had this requirement during SQL Server 2000 days, in a HR system.

If you search for this in Google followings are the results.

image

There are around 2,350,000 results!. In these results, there you might they have used DATEDIFF and DATEADD functions.

With Denali CTP3 (not sure whether this was there in CTP1) new function called EOMonth is included. Now it is simply matter of passing date.

You can see the a syntax in the following image. Point to note here is there is no execution plan differences between this version and the previous. So only difference will be the usability,

image

2 comments:

  1. Linkedin Group: Microsoft MVP - Professional Group
    Discussion:Function for End of Month

    Good post Dinesh, this happens quite frequently and is requested by management for key reporting deliverables or milestones that will need to be posted and reviewed on last day of any given month. Thanks, Tim
    Posted by Tim Runcie, MCTS, MCP, PMP, MVP

    ReplyDelete
  2. Group: Microsoft MVP - Professional Group
    Discussion:Function for End of Month

    If you are in Access, all you need is this:

    DateSerial(Year(Now()), Month(Now()) + 1, 0)
    Posted by Joe Anderson

    ReplyDelete