How to create dates from date components in SQL (T-SQL)?

Question

How can I construct native date data type values in SQL (T-SQL)?

I've added some examples, but please provide your own. My examples assume that the month and year are being stored (or are readily available) as integer values, but maybe your example will assume that the day and the month (or whatever) are stored as text. I can't see the future; surprise me.

Answer 1

SELECT DATEFROMPARTS(@Year, @Month, @Day)

(From SQL Server 2012)

Answer 2

Why, with input data as strings one of the most obvious (and therefore hardly surprising, sorry) solutions would be:

SELECT
  mydate = CAST([year] + RIGHT('0' + [month], 2) + '01' AS datetime)
                                           /* or 'AS date' in SQL Server 2008+ */
FROM (
  SELECT [month] = '2',  [year] = '2011' UNION ALL
  SELECT [month] = '03', [year] = '2011' UNION ALL
  SELECT [month] = '5',  [year] = '2011' UNION ALL
  SELECT [month] = '12', [year] = '2011' UNION ALL
  SELECT [month] = '8',  [year] = '2084' UNION ALL
  SELECT [month] = '1',  [year] = '1940'
) x;