|
|
Posted by Stu on 06/27/06 00:03
Actually a calendar table is pretty simple to use, and very effecient
for situations like these. If you haven't used one before, it's very
simple to set up. Check out http://www.aspfaq.com/show.asp?id=2519
ujjc001 wrote:
> 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
[Back to original message]
|