In my last blog post I have shown 10 ways to simulate DateSerial function.
Jeff Moden, SQL Server MVP, legend, mentor, Author, Exceptional DBA award winner and my friend asked me

"Which is fastest in 2008? 2012?"

Here is the analysis. If you want to run this code, do it in TEST server and don't use it in PRODUCTION server.

Create a temp table #t

create table #t(year_col int,month_col int,day_col int)
GO

Populate 1 Million sample data

insert into #t (year_col,month_col,day_col)

select 
	top 1000000 
		abs(checksum(newid()))%10000 as year_value,
		abs(checksum(newid()))%100/12 as month_value ,
		abs(checksum(newid()))%100/30 as day_col 
from 
	sys.objects as so1 cross join
	sys.objects as so2 cross join
	sys.objects as so3 cross join
	sys.objects as so4

Note that some columns may have invalid data for year,month or day. So identify them and make them valid

update
	#t
set
	year_col= case when year_col <1753 then 1753 else year_col end,
	month_col= case when month_col >11 then 12 else month_col+1 end,
	day_col= case when day_col >27 then 28 else day_col+1 end

	GO

Now Enable Statistics time and compare the time taken by each method

SET STATISTICS TIME ON 
print '======================================================'
Print 'Method 1 starts'
print '======================================================'
select 
	convert(datetime,cast(day_col as varchar(2))+'/'+cast(month_col as varchar(2))+'/'+cast(year_col as varchar(4)),103)
from
	#t 
print '======================================================'
Print 'Method 1 ends'
print '======================================================'
print '======================================================'
Print 'Method 2 starts'
print '======================================================'

select 
	convert(datetime,replace(str(year_col,4)+ str(month_col,2)+str(day_col,2),' ',0),112)
from
	#t 
print '======================================================'
Print 'Method 2 ends'
print '======================================================'

print '======================================================'
Print 'Method 3 starts'
print '======================================================'

select 
	convert(datetime,concat(day_col,'/',month_col,'/',year_col) ,103)
from
	#t 

print '======================================================'
Print 'Method 3 ends'
print '======================================================'

print '======================================================'
Print 'Method 4 starts'
print '======================================================'

select
	convert(datetime,ltrim(day_col)+'/'+ltrim(month_col)+'/'+ltrim(year_col),103)
from
	#t

print '======================================================'
Print 'Method 4 ends'
print '======================================================'

print '======================================================'
Print 'Method 5 starts'
print '======================================================'

select
	dateadd(day,day_col-1,dateadd(month,month_col-1,dateadd(year,year_col-1900,0)))
from
	#t

print '======================================================'
Print 'Method 5 ends'
print '======================================================'

print '======================================================'
Print 'Method 6 starts'
print '======================================================'

select
	dateadd(month,month_col-1,dateadd(year,year_col-1900,0))+day_col-1
from
	#t

print '======================================================'
Print 'Method 6 ends'
print '======================================================'

print '======================================================'
Print 'Method 7 starts'
print '======================================================'

select
	dateadd(month,(year_col-1900)*12+month_col-1,0)+day_col-1
from
	#t
	
print '======================================================'
Print 'Method 7 ends'
print '======================================================'

print '======================================================'
Print 'Method 8 starts'
print '======================================================'

select
	cast(datefromparts(year_col,month_col,day_col) as datetime)
from
	 #t
	 	
print '======================================================'
Print 'Method 8 ends'
print '======================================================'

print '======================================================'
Print 'Method 9 starts'
print '======================================================'

select
	datetimefromparts(year_col,month_col,day_col,0,0,0,0) 
from
	 #t
	
print '======================================================'
Print 'Method 9 ends'
print '======================================================'

print '======================================================'
Print 'Method 10 starts'
print '======================================================'

select
	cast(ltrim(year_col*10000+month_col*100+day_col) as datetime)
from
	#t

print '======================================================'
Print 'Method 10 ends'
print '======================================================'

SET STATISTICS TIME OFF 

Here is the data in tabular format

==============================================
		CPU Time        Elapsed time  
==============================================

Method 1		1809 ms		10700 ms		

Method 2		7582 ms		8781 ms		

Method 3		1342 ms		7458 ms

Method 4		1529 ms		7966 ms

Method 5		702 ms		9071 ms

Method 6		483 ms		8066 ms

Method 7		468 ms		8045 ms

Method 8		359 ms		8084 ms

Method 9		468 ms		8076 ms

Method 10	   858 ms		7932 ms
==============================================

In version 2012, Method 8 seems faster. It is becuase it only accepts three parameters and takes lesser time to convert them into datetime value.

In versions prior to 2012, Method 7 seems faster.(convert year value into month). An extra DATEADD function for a year is not needed when a year value is converted to month value.

It should be also noted that all CHARACTER convertions are slowing than Method 10 that uses arthmetic operators and convert the result into varchar and into datetime value. Only one explicit convertion happens before being converted to datetime

Note : All these were tested using SQL Server 2012 Express edition.