<p>I am trying to convert a date with individual parts such as 12, 1, 2007 into a datetime in SQL Server 2005. I have tried the following:</p>

<pre><code>CAST(DATEPART(year, DATE)+'-'+ DATEPART(month, DATE) +'-'+ DATEPART(day, DATE) AS DATETIME)
</code></pre>

<p>but this results in the wrong date. What is the correct way to turn the three date values into a proper datetime format.</p>

Try CONVERT instead of CAST.
CONVERT allows a third parameter indicating the date format.
List of formats is here:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Update after another answer has been selected as the "correct" answer:
I don't really understand why an answer is selected that clearly depends on the NLS settings on your server, without indicating this restriction.