DateSerial function accepts three paramter values year,month and day and return a valid date value with time set to midnight. SQL Server does not support this function until version 2012 is released.

There can be many ways to simulate this functionality. Here are 10 different ways

Consider the following set of data

create table #t(year_col int,month_col int,day_col int)
GO
insert into #t (year_col,month_col,day_col)
select 2001,6,2

Character Handling

Method 1 : Convert the numbers to varchars and into datetime

select 
	convert(datetime,cast(day_col as varchar(2))+'/'+cast(month_col as varchar(2))+'/'+cast(year_col as varchar(4)),103)
from
	#t 

Method 2 : Use STR function and Convert into datetime

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

Method 3 : Use CONCAT function and Convert into datetime(from version 2012 onwards)

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

Method 4 : Make use of implicit varchar convertion and Convert into datetime

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

Date handling using DATEADD functions

Method 5 : Use DATEADD function

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

Method 6 : Use DATEADD function(shortcut to Method 5)

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

Method 7 : Use DATEADD function (convert year value into month)

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

Method 8 : Use DATEFROMPARTS function (from version 2012 onwards)

select
	cast(datefromparts(year_col,month_col,day_col) as datetime)
from
	 #t

Method 9 : Use DATETIMEFROMPARTS function (from version 2012 onwards)

select
	datetimefromparts(year_col,month_col,day_col,0,0,0,0) 
from
	 #t

Arithmetic operation

Method 10 : Use Arithmetic operators and convert to datetime

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

-----------------------
2001-06-02 00:00:00.000