Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Convert Query to TSQL UDF

Posted on 2012-04-12
5
Medium Priority
?
399 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 1600 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 400 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 400 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

577 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