Convert Query to TSQL UDF

I'm havin an issue converting a working query to a sql server user defined function.
The function takes two dates and returns a table with the difference between the two in each date part no matter how the dates are formatted.
For example if the dates were 1992-09-06 00:00:00 and 19941212 00:00:00 it would return

years    months   days    hours     minutes
---------   ------------    -------    --------     --------------
   2           3             6         0            0

It also returns a specific part if you specify it as a third parameter.

This query is working perfectly as a stand alone query but when I try to put it into a UDF I get the error message: "Incorrect syntax near 'BEGIN'.

Attached are the working query and the UDF.

Can someone let me know what I'm doing wrong?
getFullAgeFromTwoDates2.txt
getFullAgeFromTwoDates2-UDF.txt
Garbonzo_HorowitzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SJCFL-AdminCommented:
I do not think you are clear on user defined functions.  You need to define what is being returned and then select the values into the defined fields.  I've partly modified the code. You can see where the returns clause needs to describe what is being returned.  (You need to add the types varchar, whatever)    Then down below in The select, you should precede it with an insert so that the results are placed into the returned table structure for the udf to pass back to the code from whence it was called.....  you were almost there. Just that little piece missing.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            
-- Create date: 4/12/2012
-- Description:      Returns date diff parts between 2 given dates
-- =============================================
CREATE FUNCTION dbo.termDiff
(
      @dOne datetime,
      @dDos datetime,
      @thePart varchar(15)
)
RETURNS @MyTable TABLE
(
years ....,
months.....,
days....,
hours....,
minutes....
)
AS
BEGIN -- main

      -- Fill the table variable with the rows for your result set
IF @thePart = 'all' OR @thePart = ''
BEGIN
insert into @MyTable
select
years, months,
case
      when day(@dOne)>day(@dDos)
      then day(@dDos)+
      datediff(day,@dOne,dateadd(month,datediff(month,0,@dOne)+1,0))-1
      else day(@dDos)-day(@dOne)
end as days,

datepart(hour,convert(varchar(10),dateadd(minute,minutes,0),108)) as hours,
datepart(minute,convert(varchar(10),dateadd(minute,minutes,0),108)) as minutes

from
(
      select
      years,
      datediff(month,dateadd(year,years,@dOne),@dDos)+
      case
            when day(@dDos)>=day(@dOne)
            then 0
            else -1
      end as months,
      
      datediff(minute,convert(varchar(8),@dOne,108),
      convert(varchar(8),@dDos,108)) as minutes
      from
      (
            select
            datediff(year,@dOne,@dDos) +
            case
                  when month(@dDos)>=month(@dOne)
                  then 0
                  else -1
            end as years
      ) as t
) as t

END

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
In other words, if you want to use your SQL script your UDF cannot be an Inline function as you have coded it, it is to be a Multi-Statement function.
SJCFL-AdminCommented:
ac, would you say this is an appropriate representation for the two different types of functions?

Inline Function:  This is very simple. The returned table is not explicitly defined. the function is expressed as a basic select statement.

Multi-Statement function: More complicated (and can be less efficient). Must define the table to be returned and populate it's values within your code. Must include a BEGIN/END block.
Anthony PerkinsCommented:
That looks right to me.  Though I would add:  In general and for performance reasons, Multi-statement UDFs should be avoided like the plague. :)

But I think you said that...
Garbonzo_HorowitzAuthor Commented:
Thanks. Sorry for the delay in getting back to this question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.