|
|
Posted by ujjc001 on 06/26/06 23:54
for some reason that sounds painful. Well I went with the simple case
statement inline, which works nicely just takes up a lot more room in
the sp. Another note, I did need the cast and converts to get my date
in a nice format for reporting purposes. I don't recally my exact
reasoning why, but it would have been much more difficult in crystal to
format my report the way I needed by passing in the raw data or for
that mater, anything w/ time on the end. The cast converts remove the
time too.
Thanks for the help.
Jeff
Stu wrote:
> 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
Navigation:
[Reply to this message]
|