Many flavours of Visual Basic include a DateSerial function that takes seperate year, month and day numbers and returns a date. T/SQL does not include this useful function so here's an implementation of it.

For an extreme example of optimising T/SQL expressions see the Make Date function (like in VB) post in the SQLTeam forums. Some quick testing in SQL Server 2005 shows little difference in performance so this could be a case of readable code being better than fastest code.

This function is suitable for SQL Server 2005 and 2008 as well as SQL Server 2000 (just remove the RETURNS NULL ON NULL INPUT function option).

create function dbo.udf_DateSerial (@year int, @month int, @day int) returns datetime with returns null on null input 
/*
** Return a datetime for a given year, month, day.
*/
as
begin

   return dateadd (dd, @day - 1, dateadd(mm, @month - 1, dateadd(yy, @year - 1900, 0)))

end
go

-- Tests / Examples
print dbo.udf_DateSerial(1753, 1, 1)   -- Earliest datatime date
print dbo.udf_DateSerial(2008, 2, 29)  -- 29th Feb
print dbo.udf_DateSerial(9999, 12, 31) -- Latest datatime date
go