Solved

Convert Query to TSQL UDF

Posted on 2012-04-12
5
394 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:Garbonzo_Horowitz
  • 2
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
SJCFL-Admin earned 400 total points
ID: 37838833
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
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 37840773
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.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37843163
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.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 37844122
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...
0
 

Author Closing Comment

by:Garbonzo_Horowitz
ID: 37861386
Thanks. Sorry for the delay in getting back to this question.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question