Update:  Please refer to this page for an update on this concept; I recommend that you use the newer functions presented on that page.

To me, this is a pretty handy function to have in your toolbox:

Create function MDate(@Year int, @Month int, @Day int)

  returns datetime

AS

  BEGIN

  declare @d datetime;

  set @d = dateadd(year,(@Year - 1753),'1/1/1753');

  set @d = dateadd(month,@Month - 1,@d);

  return dateadd(day,@Day - 1,@d)

END

This function basically lets you build dates exactly as you can in VB using the DateSerial() function -- just pass in a year, a month, and a day.

The idea is you can use this function to perform “date math” really easily, without worrying about going over month or year borders.  Everything is relative (like DateSerial in VB), and you can add 1 to the 12th month to get the 1st month of the following year, and things like that. 

So, if @Date is a date, you can easily calculate things like:

• MDate(Year(@Date),Month(@Date),1) -- the 1st day of the month
• MDate(Year(@Date),Month(@Date)+1,1)-1  -- the last day of the month
• MDate(Year(@Date),12,31) -- the last day of the year
• MDate(Year(@Date),Month(@Date)+6,1) -- the first day of the month six months from @Date.
• MDate(Year(@Date),Month(@Date),1)-1 -- last day of previous month

Try it out -- I hope you'll find it very useful!  If nothing else, it'll make your code much shorter!  Just adding this one function to your T-SQL toolbox will allow you to handle and manipulate dates in a much easier manner.

(Again: do not use this function if your need is for MDate(2003,13,34) to return an error -- use it if you need it to return Feb 3, 2004.)