Posted by Mike C# on 06/22/06 23:16

How about an inline table-valued UDF? And getting rid of all those CASTs
and CONVERTs... After all you're inputting a DATETIME and returning a
DATETIME:

CREATE FUNCTION dbo.TimeFormat
(
@input DATETIME,
@groupformat VARCHAR(20)
)
RETURNS TABLE
AS
RETURN
(
SELECT CASE @groupformat
WHEN 'DAY' THEN @input
WHEN 'WEEK' THEN DATEADD(day, 1 - DATEPART(dw, @input), @input)
WHEN 'MONTH' THEN DATEADD(day, -DATEPART(d, @input)+1, @input)
END AS [XDate]
)

SELECT XDate
FROM dbo.TimeFormat ('2006-06-22', 'DAY')

SELECT XDate
FROM dbo.TimeFormat ('2006-06-22', 'WEEK')

SELECT XDate
FROM dbo.TimeFormat ('2006-06-22', 'MONTH')

"ujjc001" <ujjc001@gmail.com> wrote in message
news:1151013715.191251.45940@i40g2000cwc.googlegroups.com...
> Hello all-
> Given the following UDF, in sql 2000 can it be sped up, complied or
> anything of the like. A query returning 300,000 + rows times out when
> ran through the udf, inline case statements returns the rows in 5
> seconds.
> Thanks!
> Jeff
>
> CREATE FUNCTION dbo.TimeFormat
> (
> @input datetime,
> @groupformat varchar(20) --DAY, WEEK, MONTH
> )
>
> RETURNS datetime
>
> AS
>
> BEGIN
> declare @dtvar as datetime
>
> if @groupformat = 'DAY'
> set @dtvar = CAST(CONVERT(char(10), @input, 101) AS datetime)
> else if @groupformat = 'WEEK'
> set @dtvar = CAST(DATEADD([DAY], 1 - DATEPART(dw, CONVERT(char(10),
> @input, 101)), CONVERT(char(10), @input, 101)) AS datetime)
> else if @groupformat = 'MONTH'
> set @dtvar = CAST(CONVERT(CHAR(6), @input, 112) + '01' AS datetime)
> return @dtvar
> END
>

• Next in forum: Separate databases for high/low transaction volumes?
• Prev in forum: Re: Very Poor Performance - Identical DBs but Different Performance
• Thread view: Re: Speed up UDF

[Reply to this message]