Posted by Stu on 06/24/06 05:16

Another alternative would be to modify a calendar table in such a way
as to return the values you want. You could then join on the calendar
table (using the date value as a lookup) and return the start day of
the week or month as needed.

Stu


ujjc001 wrote:
> 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

[Back to original message]