In Part I of this series SQL Server's equivalent of MS Access functions, I have posted some direct equivalents. In this post, I post other functions that dont have direct equivalent

Choose : Returns a value from the list based on a given position

declare @list varchar(1000)
declare @select varchar(8000)
declare @t table(list_no int identity(1,1),list varchar(100))
set @select=''
set @list = '"test1","test2","test3","test4"'

set @select= 'select '''+REPLACE(@list,'","','"'' union all select ''"')+''''
insert into @t 
exec(@select)

select list from @t 
where list_no=3

Datevalue : Converts a string to a valid date

select CAST('june30 2010' as datetime)
select CAST('6/30/2010' as datetime)

Timevalue : Converts a string to a Time

select convert(varchar(12),cast('3:12:57 PM' as datetime),108)
select cast(@hour as VARCHAR(2))+':'+RIGHT('0'+cast(@minute as VARCHAR(2)),2)+':'+RIGHT('0'+cast(@second as VARCHAR(2)),2)

Dateserial : Make a valid date based on the year,month and day

declare @year int, @month int, @day int
select @year =2010,@month =6,@day =30
select dateadd(year,@year-1900,dateadd(month,@month-1,dateadd(day,@day ,0)))
select cast(cast(@year  as VARCHAR(4))+RIGHT('0'+cast(@month as VARCHAR(2)),2)+RIGHT('0'+cast(@day as VARCHAR(2)),2) as datetime)