Solved

Convert Query to TSQL UDF

Posted on 2012-04-12
5
391 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now