Create a date from day month and year with T-SQL

Assuming y, m, d are all int, how about:

CAST(CAST(y AS varchar) + '-' + CAST(m AS varchar) + '-' + CAST(d AS varchar) AS DATETIME)

Please see my other answer for SQL Server 2012 and above

Or using just a single dateadd function:

DECLARE @day int, @month int, @year int
SELECT @day = 4, @month = 3, @year = 2011

SELECT dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1)

SQL Server 2012 has a wonderful and long-awaited new DATEFROMPARTS function (which will raise an error if the date is invalid - my main objection to a DATEADD-based solution to this problem):

http://msdn.microsoft.com/en-us/library/hh213228.aspx

DATEFROMPARTS(ycolumn, mcolumn, dcolumn)

or

DATEFROMPARTS(@y, @m, @d)

Try this:

Declare @DayOfMonth TinyInt Set @DayOfMonth = 13
Declare @Month TinyInt Set @Month = 6
Declare @Year Integer Set @Year = 2006
-- ------------------------------------
Select DateAdd(day, @DayOfMonth - 1, 
          DateAdd(month, @Month - 1, 
              DateAdd(Year, @Year-1900, 0)))

It works as well, has added benefit of not doing any string conversions, so it's pure arithmetic processing (very fast) and it's not dependent on any date format This capitalizes on the fact that SQL Server's internal representation for datetime and smalldatetime values is a two part value the first part of which is an integer representing the number of days since 1 Jan 1900, and the second part is a decimal fraction representing the fractional portion of one day (for the time) --- So the integer value 0 (zero) always translates directly into Midnight morning of 1 Jan 1900...

or, thanks to suggestion from @brinary,

Select DateAdd(yy, @Year-1900,  
       DateAdd(m,  @Month - 1, @DayOfMonth - 1)) 

Edited October 2014. As Noted by @cade Roux, SQL 2012 now has a built-in function:
DATEFROMPARTS(year, month, day)
that does the same thing.

Edited 3 Oct 2016, (Thanks to @bambams for noticing this, and @brinary for fixing it), The last solution, proposed by @brinary. does not appear to work for leap years unless years addition is performed first

select dateadd(month, @Month - 1, 
     dateadd(year, @Year-1900, @DayOfMonth - 1));