February 20, 2014 at 9:37 am
Hi All,
i am using Below Finctinon,
USE [ICB_TEST]
GO
/****** Object: UserDefinedFunction [dbo].[asString_New] Script Date: 02/20/2014 19:56:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[asString_New](
@pYY int, @pMM int, @pdd int,
@pHH int, @pMI int, @pss int,@Operator Varchar(100)) returns varchar(32)
as
begin
declare @d varchar (32)
declare @F varchar (32)
declare @s-2 varchar(32)
declare @Diff int
set @d = ltrim(str(@pYY))
set @d = replicate('0', 4 - len(@d)) + @d
set @s-2 = ltrim(str(@pMM))
set @s-2 = replicate('0', 2 - len(@s)) + @s-2
set @d = @d + '-' + @s-2
set @s-2 = ltrim(str(@pDD))
set @s-2 = replicate('0', 2 - len(@s)) + @s-2
set @d = @d + '-' + @s-2
set @s-2 = ltrim(str(@pHH))
set @s-2 = replicate('0', 2 - len(@s)) + @s-2
set @d = @d + ' ' + @s-2
set @s-2 = ltrim(str(@pMI))
set @s-2 = replicate('0', 2 - len(@s)) + @s-2
set @d = @d + ':' + @s-2
set @s-2 = ltrim(str(@pSS))
set @s-2 = replicate('0', 2 - len(@s)) + @s-2
set @d = @d + ':' + @s-2
Select @Diff=Timediff From Carriers (nolock)
Where DisplayName=@Operator
Select @d= DateAdd(HH,@Diff,@d)
return @D
end
Here the return value is comming like Feb 2 2014 5:00AM instead of 2014-02-02 05:00:00.000 please help to solve this ...
February 20, 2014 at 9:48 am
The problem is that @d is a varchar. To use DATEADD, SQL Server converts @d to datetime and then back to varchar to assign it to @d. The second conversion is using the default format (format code 1). To prevent this, you need to use CONVERT with a format code.
There might be better ways to do this as well.
February 20, 2014 at 9:55 am
try this while assigning the final value to @d
Select @d= convert(varchar,DateAdd(HH,@Diff,@d),120)
February 20, 2014 at 9:57 am
To complete previous post.
If you change your scalar function into an inline table-valued function, your queries should perform a lot better. Check this article for more information:
This is an example that you can explore:
CREATE FUNCTION [dbo].[asString_New_InLine](
@pYY int = 2014,
@pMM int = 2,
@pdd int = 2,
@pHH int = 5,
@pMI int = 0,
@pss int = 0,
@Operator Varchar(100)
)
RETURNS table AS
RETURN
SELECT CONVERT( char(23), DateAdd(HH, Timediff, RIGHT( '000' + CONVERT( varchar(4), @pYY), 4)
+ RIGHT( '0' + CONVERT( varchar(2), @pMM), 2)
+ RIGHT( '0' + CONVERT( varchar(2), @pdd), 2) + ' '
+ RIGHT( '0' + CONVERT( varchar(2), @pHH), 2) + ':'
+ RIGHT( '0' + CONVERT( varchar(2), @pMI), 2) + ':'
+ RIGHT( '0' + CONVERT( varchar(2), @pss), 2)), 121) AS StringDate
From Carriers
Where DisplayName=@Operator
February 21, 2014 at 1:50 am
Brilliant it's working Fine...thanks a lot..
February 21, 2014 at 8:30 am
Luis Cazares (2/21/2014)
You're welcome.Do you understand how and why it works?
I also missed the link in my previous post. Here it is: How to Make Scalar UDFs Run Faster (SQL Spackle)[/url]
We all know that he or someone with a similar question will just want the answer and not understand why it didn't work the first time, so many people just want to be fed fish but not learn how to fish 😛
February 21, 2014 at 8:35 am
stormsentinelcammy (2/21/2014)
Luis Cazares (2/21/2014)
You're welcome.Do you understand how and why it works?
I also missed the link in my previous post. Here it is: How to Make Scalar UDFs Run Faster (SQL Spackle)[/url]
We all know that he or someone with a similar question will just want the answer and not understand why it didn't work the first time, so many people just want to be fed fish but not learn how to fish 😛
+1.
I've noticed over the last while that the number of posts where the OP doesn't thank the solution provider(s) or respond to questions such as Luis' seem to be increasing. Shame.
February 21, 2014 at 8:53 am
To be fair, (s)he might be in a different time zone.
February 21, 2014 at 8:58 am
Luis Cazares (2/21/2014)
To be fair, (s)he might be in a different time zone.
True. I wasn't referring to this particular post, just making a general observation.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply