How can I retrieve a function return value from an executed string?

Hello!

Suppose that I have the name of an UDF (@.Function) which returns an scalar value and accepts just one parameter. How can I execute and assign the return value to a variable?

I tried the following, but it did not work:

declare @.Receptor as sql_variant

set @.Receptor=execute('select ' + @.Function + '(10)')

Thanks a lot in advance.The function is not completely qualified (DB name, owner)
Try:

declare @.Receptor as sql_variant
set @.Receptor = database_name.dbo.Function(@.in_param_value)

A user defined function can be used like any other pre-defined function

Originally posted by EMoscosoCam
Hello!

Suppose that I have the name of an UDF (@.Function) which returns an scalar value and accepts just one parameter. How can I execute and assign the return value to a variable?

I tried the following, but it did not work:

declare @.Receptor as sql_variant

set @.Receptor=execute('select ' + @.Function + '(10)')

Thanks a lot in advance.sql

How can I process each row in result set to access properties on another package o

Take a look at the DynamicProperties task. This will allow you to set DTS
properties based query that returns a scalar value. You'll need to specify
a separate query for each property.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I have a DTS package that I'm working with, in which I have a query that I
want to invoke
> on a target SQL Server that will return a handful of rows. For each row,
I want to set
> some package properties (on another object in the package). What would be
the best
> approach to this? I thought that I might use the "Transform Data Task",
even though I
> don't really have a "Destination", per se (that is, I want to process each
"Source" record
> via an ActiveX script).
> However, when I try and do this, I seem to be getting an error when I
execute that
> "Transform Data Task" step (something akin to "Execution Cancelled by
User").
> Is there some other way that I should approach this?
> Regards,
> John Peterson
>Thanks, Dan -- but I can't seem to get my head around your suggestion. Basi
cally, what I
want is to be able to specify a Source Query that would return a bunch of ro
ws. Then, for
each row, I want to invoke some ActiveX snippet withOUT doing anything to a
"Destination".
I don't see that it's too easy with DTS...
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> Take a look at the DynamicProperties task. This will allow you to set DTS
> properties based query that returns a scalar value. You'll need to specif
y
> a separate query for each property.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> want to invoke
> I want to set
> the best
> even though I
> "Source" record
> execute that
> User").
>|||In article <OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl>, "John Peterson" <j0hnp@.comcast.net> wrot
e:
>Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
>want is to be able to specify a Source Query that would return a bunch of r
ows.
> Then, for
>each row, I want to invoke some ActiveX snippet withOUT doing anything to a
> "Destination".
>I don't see that it's too easy with DTS...
>
Just do it in a VBScript task.
Open a recordset.
Loop thru it and do whatever you want during each loop.|||> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
Sorry, but I don't understand what you mean by <withOUT doing anything to a
"Destination">. Please elaborate.
If you want to assign many properties from a single query, below is an
example of the ActiveX script technique suggested by b_43@.hotmail.com.
CREATE TABLE DTSPackageProperties
(
PackageName varchar(255) NOT NULL,
ObjectName varchar(255) NOT NULL,
PropertyName varchar(255) NOT NULL,
PropertyValue varchar(255) NOT NULL,
)
ALTER TABLE DTSPackageProperties
ADD CONSTRAINT PK_DTSPackageProperties
PRIMARY KEY(PackageName, ObjectName, PropertyName)
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MySource', 'DataSource',
'C:\InputFiles\MyInputFile.txt')
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MyDestination', 'DataSource',
'C:\OutputFiles\MyOutputFile.txt')
Function Main()
Dim conn, rs, sqlQuery
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;" & _
"Data Source=MyServer;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=MyDatabase"
sqlQuery = "SELECT ObjectName, PropertyValue"
sqlQuery = sqlQuery + " FROM DTSPackageProperties"
sqlQuery = sqlQuery + " WHERE PackageName = '"
sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
Set rs = conn.Execute(sqlQuery)
Do While rs.EOF = False
DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataSou
rce = _
rs.Fields("PropertyValue").Value
rs.MoveNext
Loop
rs.Close
conn.Close
Set rs = Nothing
Set comm = Nothing
Main = DTSTaskExecResult_Success
End Function
The alternative DynamicProperties task method would use the following
queries to assign the properties.
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MySource' AND
PropertyName = 'DataSource'
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MyDestination' AND
PropertyName = 'DataSource'
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
DTS[vbcol=seagreen]
specify[vbcol=seagreen]
that I[vbcol=seagreen]
row,[vbcol=seagreen]
would be[vbcol=seagreen]
Task",[vbcol=seagreen]
each[vbcol=seagreen]
>|||Thanks Dan (and bb_43)!
I had hoped there would have been a simpler solution in the context of exist
ing DTS
objects, rather than having to write a lot of code. Alas, it seems like it'
s not quite
the case, even though DTS seems uniquely qualified to do this type of thing
(almost).
Since it can use a Connection to issue a query on that remote server and pro
cess the rows.
The only problem is that both the "Transform Data Task" and "Data Driven Que
ry Task" seem
to *require* a "destination" object; that you can't simply have an ActiveX t
ransformation
script for each row without having the data ultimately going somewhere.
Thanks again!
John Peterson
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:Oj%23iSG9SEHA.3476@.tk2msftngp13.phx.gbl...
> Basically, what I
> rows. Then, for
> a "Destination".
> Sorry, but I don't understand what you mean by <withOUT doing anything to
a
> "Destination">. Please elaborate.
> If you want to assign many properties from a single query, below is an
> example of the ActiveX script technique suggested by b_43@.hotmail.com.
>
> CREATE TABLE DTSPackageProperties
> (
> PackageName varchar(255) NOT NULL,
> ObjectName varchar(255) NOT NULL,
> PropertyName varchar(255) NOT NULL,
> PropertyValue varchar(255) NOT NULL,
> )
> ALTER TABLE DTSPackageProperties
> ADD CONSTRAINT PK_DTSPackageProperties
> PRIMARY KEY(PackageName, ObjectName, PropertyName)
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MySource', 'DataSource',
> 'C:\InputFiles\MyInputFile.txt')
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MyDestination', 'DataSource',
> 'C:\OutputFiles\MyOutputFile.txt')
> Function Main()
> Dim conn, rs, sqlQuery
> Set conn = CreateObject("ADODB.Connection")
> conn.Open "Provider=SQLOLEDB;" & _
> "Data Source=MyServer;" & _
> "Integrated Security=SSPI;" & _
> "Initial Catalog=MyDatabase"
> sqlQuery = "SELECT ObjectName, PropertyValue"
> sqlQuery = sqlQuery + " FROM DTSPackageProperties"
> sqlQuery = sqlQuery + " WHERE PackageName = '"
> sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
> sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
> Set rs = conn.Execute(sqlQuery)
> Do While rs.EOF = False
> DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataS
ou
> rce = _
> rs.Fields("PropertyValue").Value
> rs.MoveNext
> Loop
> rs.Close
> conn.Close
> Set rs = Nothing
> Set comm = Nothing
> Main = DTSTaskExecResult_Success
> End Function
> The alternative DynamicProperties task method would use the following
> queries to assign the properties.
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MySource' AND
> PropertyName = 'DataSource'
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MyDestination' AND
> PropertyName = 'DataSource'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Basically, what I
> rows. Then, for
> a "Destination".
> DTS
> specify
> that I
> row,
> would be
> Task",
> each
>|||John,
if you do want to use the Transform Data Task without inserting rows you can
change the DTSTransformStatus constant from DTSTransformStat_OK to
DTSTransformStat_SkipInsert.
HTH,
Paul Ibison|||<blush> I did not know such a return value existed! Thanks so much, Paul -
- I'm sure
that'll do the trick! (And I think you pegged my issue *exactly*!)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23MUhxx$SEHA.2128@.TK2MSFTNGP11.phx.gbl...
> John,
> if you do want to use the Transform Data Task without inserting rows you c
an
> change the DTSTransformStatus constant from DTSTransformStat_OK to
> DTSTransformStat_SkipInsert.
> HTH,
> Paul Ibison
>|||No problem. FYI I came across this info from this book which is the most
comprehensive DTS book I know of:
_2_1/202-5145180-8774263" target="_blank">http://www.amazon.co.uk/exec/obidos...5145180-8774263
Regards,
Paul Ibisonsql

How can I keep only the first 3 digits after decimal with no rounding?

I have a query with a field that returns a decimal number in it, such
as 56.995501432. I would like to display only up to the first 3
digits after the decimal point, like so: 56.995.
I have tried using the FORMAT and FORMATNUMBER functions within the
field expression using a mask of "###,###.000", but both of these want
to round the fourth decimal up, so that the end result is, 56.996
which is not correct for our needs. Why on earth do the format
functions implement rounding!' Is there a way to turn this off? Is
there something like a TRUNCATE function that allows me to specify how
many decimals to keep?
Please help! I'm stuckTry this in the control (not the query) that is displaying the data:
=Left(Fields!Fieldname.Value,Instr(Fields!FieldName.Value,".")-1) + "." +
Mid(Fields!FieldName.Value,Instr(Fields!FieldName.Value)+1,3)
Where
1.Left(Fields!Fieldname.Value,Instr(Fields!FieldName.Value,".")-1) finds
the everything before the decimal
2. Mid(Fields!FieldName.Value,Instr(Fields!FieldName.Value)+1,3) finds 3
digits after the decimal.
Hope this helps!
Michael
"Jerry H." wrote:
> I have a query with a field that returns a decimal number in it, such
> as 56.995501432. I would like to display only up to the first 3
> digits after the decimal point, like so: 56.995.
> I have tried using the FORMAT and FORMATNUMBER functions within the
> field expression using a mask of "###,###.000", but both of these want
> to round the fourth decimal up, so that the end result is, 56.996
> which is not correct for our needs. Why on earth do the format
> functions implement rounding!' Is there a way to turn this off? Is
> there something like a TRUNCATE function that allows me to specify how
> many decimals to keep?
> Please help! I'm stuck
>|||"Michael C" wrote:
> Try this in the control (not the query) that is displaying the data:
> =Left(Fields!Fieldname.Value,Instr(Fields!FieldName.Value,".")-1) + "." +
> Mid(Fields!FieldName.Value,Instr(Fields!FieldName.Value)+1,3)
> Where
> 1.Left(Fields!Fieldname.Value,Instr(Fields!FieldName.Value,".")-1) finds
> the everything before the decimal
> 2. Mid(Fields!FieldName.Value,Instr(Fields!FieldName.Value)+1,3) finds 3
> digits after the decimal.
> Hope this helps!
> Michael
> "Jerry H." wrote:
> > I have a query with a field that returns a decimal number in it, such
> > as 56.995501432. I would like to display only up to the first 3
> > digits after the decimal point, like so: 56.995.
> >
> > I have tried using the FORMAT and FORMATNUMBER functions within the
> > field expression using a mask of "###,###.000", but both of these want
> > to round the fourth decimal up, so that the end result is, 56.996
> > which is not correct for our needs. Why on earth do the format
> > functions implement rounding!' Is there a way to turn this off? Is
> > there something like a TRUNCATE function that allows me to specify how
> > many decimals to keep?
> >
> > Please help! I'm stuck
> >
> >
or) in the properties of the field, in format type : N3
or) use the round function round(field!...,3)
if you don't want rounding ?
=cint(field!...*1000)/1000
hope this helps

How can I get value from data flow control?

How can I get value from data flow control when it returns to the Control flow?

I want to use values in the data flow to decide what I'm doing next - using an expression on the 'line' (i.e. @.Step == 10, 20 , 30 ...)
@.Step - is user defined variable.

How can I set @.Step according to the data flow ?

It seems to be that I can't change @.Step in the data flow.
Its not clear what you mean by set @.step according to the data flow. According to what? In any event most components do no set variable values in the dataflow they just look at them. The exceptions are row count and script (there could be another but I don't recall it off the top of my head). Row count only sets a variable at post execute. The script component can set a variable whenever it wants but in order to do so it can not use the variables defined on the ReadWrite variables line as those can only be set during post execute. In order to set a variable during execution you need to use the variable dispenser to lock it and then you can set it to whatever you would like. Remember to unlock it or else anything else trying to lock it will fail.

Thanks,
Matt|||

The reason I need this is because I want to define a table of tasks and that the process will continue according to the next step on the table (not as a fix path).

I don't see a script control in the data flow - so how can I change the variable ?

Thanks,

Yossi.

|||

Where is your problem?

1. Define your variable (In this example it is named Vari )

2. Drag a Script-Component (transformation) to your DataFlow-Task.

3. Select theScript Tab

4. Enter the name of the variable (Vari ) as the value of the ReadWriteVariables.

5. Click Design Script.

7. Edit your Script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

End Sub
Public Overrides Sub PostExecute()
Me.Variables.Vari = 1234
End Sub

End Class

Notice, there is no code in the ProcessInputRow. You can delete it.

|||

Thanks, it is helpful.

I want to change the variable according to a data source.

How can I access the data source which was entering to the Script-Component within the script?

.

|||

Check the columns you need in the Input Columns.

and then edit your Script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Dim Max As Integer

Public Overrides Sub PreExecute()
Max = 0
End Sub

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
If Row.InputColumn > Max Then Max = CInt(Row.InputColumn)
End Sub

Public Overrides Sub PostExecute()
Me.Variables.Vari = Max
End Sub
End Class

|||

I'm geting this msg:

"sub 'Input_ProcessInputRow' can not be declared 'override' because it dose not override a sub in base class"

|||

Seems like you are editing Script Task instead of Data Flow Script Transform (or maybe Data Flow Script Component, but Source instead of Transform).

Of course, it is possible the script task (or existing task, e.g. Execute SQL Task) is what you really need - depending on your needs and what you mean by "change the variable according to a data source". Could you describe it in more detail?

I also suggest reading SQL Books Online to get general understanding of SSIS architecture, the difference between tasks and transforms, etc.

How can I get value from data flow control?

How can I get value from data flow control when it returns to the Control flow?

I want to use values in the data flow to decide what I'm doing next - using an expression on the 'line' (i.e. @.Step == 10, 20 , 30 ...)
@.Step - is user defined variable.

How can I set @.Step according to the data flow ?

It seems to be that I can't change @.Step in the data flow.
Its not clear what you mean by set @.step according to the data flow. According to what? In any event most components do no set variable values in the dataflow they just look at them. The exceptions are row count and script (there could be another but I don't recall it off the top of my head). Row count only sets a variable at post execute. The script component can set a variable whenever it wants but in order to do so it can not use the variables defined on the ReadWrite variables line as those can only be set during post execute. In order to set a variable during execution you need to use the variable dispenser to lock it and then you can set it to whatever you would like. Remember to unlock it or else anything else trying to lock it will fail.

Thanks,
Matt|||

The reason I need this is because I want to define a table of tasks and that the process will continue according to the next step on the table (not as a fix path).

I don't see a script control in the data flow - so how can I change the variable ?

Thanks,

Yossi.

|||

Where is your problem?

1. Define your variable (In this example it is named Vari )

2. Drag a Script-Component (transformation) to your DataFlow-Task.

3. Select theScript Tab

4. Enter the name of the variable (Vari ) as the value of the ReadWriteVariables.

5. Click Design Script.

7. Edit your Script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

End Sub
Public Overrides Sub PostExecute()
Me.Variables.Vari = 1234
End Sub

End Class

Notice, there is no code in the ProcessInputRow. You can delete it.

|||

Thanks, it is helpful.

I want to change the variable according to a data source.

How can I access the data source which was entering to the Script-Component within the script?

.

|||

Check the columns you need in the Input Columns.

and then edit your Script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Dim Max As Integer

Public Overrides Sub PreExecute()
Max = 0
End Sub

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
If Row.InputColumn > Max Then Max = CInt(Row.InputColumn)
End Sub

Public Overrides Sub PostExecute()
Me.Variables.Vari = Max
End Sub
End Class

|||

I'm geting this msg:

"sub 'Input_ProcessInputRow' can not be declared 'override' because it dose not override a sub in base class"

|||

Seems like you are editing Script Task instead of Data Flow Script Transform (or maybe Data Flow Script Component, but Source instead of Transform).

Of course, it is possible the script task (or existing task, e.g. Execute SQL Task) is what you really need - depending on your needs and what you mean by "change the variable according to a data source". Could you describe it in more detail?

I also suggest reading SQL Books Online to get general understanding of SSIS architecture, the difference between tasks and transforms, etc.

How can I get this function be working?

How can I get this function be working?

CREATE FUNCTION MyFunc

(

@.MyDate as datetime,

@.MyTableName varchar(50),

)

RETURNS TABLE

AS

RETURN

SELECT * FROM @.MyTableName Where myDate=@.MyDate

? You can't -- passing a table name dynamically would require dynamic SQL, and dynamic SQL is not supported in UDFs... Why do you want to pass a table name dynamically, anyway? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JIM.H.@.discussions.microsoft.com> wrote in message news:d565e7f4-4b5a-4e7f-989d-3e6299fbda2c@.discussions.microsoft.com... How can I get this function be working? CREATE FUNCTION MyFunc ( @.MyDate as datetime, @.MyTableName varchar(50), ) RETURNS TABLE AS RETURN SELECT * FROM @.MyTableName Where myDate=@.MyDate|||

Ok. Thanks for the reply.

I am trying to deal with many tables with different column names. Is there any way I can keep the result set if the following command and perform some other sql command on it?

EXECUTE sp_executesql @.SQLSelectString

-- forward the result set in a table so that other procedures can read it.

|||? There are a few methods. I recommend that you read the following article: http://www.sommarskog.se/share_data.html -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JIM.H.@.discussions.microsoft.com> wrote in message news:f9f7fbde-732a-43bf-a322-001a6f668f95@.discussions.microsoft.com... Ok. Thanks for the reply. I am trying to deal with many tables with different column names. Is there any way I can keep the result set if the following command and perform some other sql command on it? EXECUTE sp_executesql @.SQLSelectString -- forward the result set in a table so that other procedures can read it.

How can I get this function be working?

How can I get this function be working?

CREATE FUNCTION MyFunc

(

@.MyDate as datetime,

@.MyTableName varchar(50),

)

RETURNS TABLE

AS

RETURN

SELECT * FROM @.MyTableName Where myDate=@.MyDate

Can you describeexactly what your problem is andexactly where you are trying to use the function. Are you getting error messages? If so, show them.

My guess is that the problem is you are trying to use a parameter as the table name. You cannot do that.

|||

Ok. Thanks for the reply.

I am trying to deal with many tables with different column names. Is there any way I can keep the result set if the following command and perform some other sql command on it?

EXECUTE sp_executesql @.SQLSelectString

-- forward the result set in a table so that other procedures can read it.

how can I get the list of tables and views together in the database by the dbconnection.ge

I have tried
SqlConnection.getSchema("Tables"). it returns me a list of all tables while getSchema("Views") returns me the views in the form of DataTable.
But can we have a way to get them together in the same DataTable as a return result? because I want to read the rows by DataTable.CreateDataReader() later.

select

Namefromsysobjectswhere typein('v','u')and status>-1

Will return a list of all the user tables and views.

|||

In SQL Server 2005, you should use system view for the same result.

select * from sys.objects where type in ('v','u')

How can I get current year in the user defined function.

I have a user defined function in datebase SQL 2000.
function looks like

create function Getcurrentdate
(@.month int, @.day int) returns smalldatetime
begin

declare date1 as smalldatetime
--get current year
--convert month, day and year into current date. then return

return date1
end

my problem was , after using getDate(). I get error meassage which is "can'not use getDate() inside user function"
How can I get current year in the user defined function. Thanks

Unfortunately, you can't use GetDate() in a UDF, here's a link thatgives details and possible work-arounds:http://www.aspfaq.com/show.asp?id=2439

|||

Hi
Here is another option a UDF doing time interval in SQL Server, I am assuming you know SmallDateTime will not give you seconds because of limited resolution. Hope this helps.

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm

|||Thanks, you guys. it's helpful