Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How many more Mondays until I retire? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, July 19, 2006 8:49 AM
 SSC Eights! Group: General Forum Members Last Login: Tuesday, December 03, 2013 2:46 PM Points: 940, Visits: 225
 Well done Peter. I like the versatility. Great examples.You could even populate the holiday table this way, at least when they are defined like the Civic Holiday or Labour Day (in Canada) as the first Monday in August and September, respectively. For holidays dependant on the lunar cycle, like Easter... whatcha gonna do?
Post #295538
 Posted Wednesday, July 19, 2006 9:04 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 6:43 AM Points: 2,378, Visits: 3,350
 The best way would be to use this functionCREATE FUNCTION dbo.GetEasterSunday (     @Y INT ) RETURNS SMALLDATETIME AS BEGIN     DECLARE     @EpactCalc INT,                  @PaschalDaysCalc INT,                 @NumOfDaysToSunday INT,                 @EasterMonth INT,                 @EasterDay INT      SET @EpactCalc = (24 + 19 * (@Y % 19)) % 30     SET @PaschalDaysCalc = @EpactCalc - (@EpactCalc / 28)     SET @NumOfDaysToSunday = @PaschalDaysCalc - ((@Y + @Y / 4 + @PaschalDaysCalc - 13) % 7)    SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40) / 44    SET @EasterDay = @NumOfDaysToSunday + 28 - (31 * (@EasterMonth / 4))      RETURN     (         SELECT CONVERT         (             SMALLDATETIME,              RTRIM(@Y)              + RIGHT('0'+RTRIM(@EasterMonth), 2)              + RIGHT('0'+RTRIM(@EasterDay), 2)         )     ) END Taken from http://www.aspfaq.com/show.asp?id=2519 N 56°04'39.16"E 12°55'05.25"
Post #295543
 Posted Wednesday, July 19, 2006 9:13 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 6:43 AM Points: 2,378, Visits: 3,350
 Yes, I did benchmark your code Razvan. Did you try your test-code yourself?Running your test-code above gives 763 milliseconds for my function and 1,563 milliseconds for your function.Mine is faster and I think that is because SQL is not very fast handling two variables as in your function. N 56°04'39.16"E 12°55'05.25"
Post #295550
 Posted Wednesday, July 19, 2006 9:26 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, November 10, 2010 7:15 AM Points: 18, Visits: 18
Post #295557
 Posted Wednesday, July 19, 2006 12:51 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, May 07, 2012 10:05 AM Points: 165, Visits: 134
 I too wrote a slightly different version...  Same binary concept, implemented slightly differently with a twist!  It allows the specification of an increment or step value.`CREATE FUNCTION dbo.udf_genDateSequence   (      @prmLoDate datetime, -- lower date boundary (starting value)      @prmHiDate datetime, -- upper date boundary (maximum value)      @prmIncDays int      -- increment value   )RETURNS @Dates TABLE (DateVal datetime NOT NULL PRIMARY KEY)AS/*Function:   Generate a table of dates.Strategy:   Set-based scalar value generation.Usage:      dbo.udf_genDateSequence(fromValue, toValue, stepValue)*/BEGIN   DECLARE      @daysDiff int,      @swapVar datetime     IF @prmLoDate > @prmHiDate         BEGIN            SET @swapVar = @prmLoDate            SET @prmLoDate = DATEADD(day, DATEDIFF(day, 0, @prmHiDate), 0)            SET @prmHiDate = DATEADD(day, DATEDIFF(day, 0, @swapVar), 0)         END      ELSE         BEGIN            SET @prmLoDate = DATEADD(day, DATEDIFF(day, 0, @prmLoDate), 0)            SET @prmHiDate = DATEADD(day, DATEDIFF(day, 0, @prmHiDate), 0)         END    SET @daysDiff = DATEDIFF(day, @prmLoDate, @prmHiDate)    INSERT INTO @Dates VALUES(@prmLoDate)    WHILE @prmIncDays <= @daysDiff      BEGIN         INSERT INTO @Dates            SELECT DATEADD(day, @prmIncDays, DateVal)               FROM @Dates               WHERE DateVal <= DATEADD(day, -@prmIncDays, @prmHiDate)          SET @prmIncDays = @prmIncDays * 2      END   RETURNENDgo`
Post #295627
 Posted Wednesday, July 19, 2006 6:54 PM
 SSChasing Mays Group: General Forum Members Last Login: Yesterday @ 3:15 PM Points: 600, Visits: 1,666

Why am I using the DATEADD and DATEDIFF functions here?
They are used to remove time information! I want to be sure that only date information is used. So I set the time to 00:00:00.000 the fastest way I know.

[ie: DATEADD(day, DATEDIFF(day, 0, getdate()), 0)        ]

I've not seen this method used before.  Although it pops up from time-to-time on this site, the "best" method I knew for stripping the time was:
cast(floor(cast(getdate() as float)) as datetime)
After some testing (with a rather clunky while loop) I got the following table:
 iterations datediff floor 1 0 0 10 0 0 100 0 0 1000 7 6 10000 68 75 100000 712 810
Iterations is the number of times the method was used, run 100 times each, and then an average taken.  Seems to be that if you're performing a low number of "time strips" there is negligible difference.  Indeed, for 100,000 iterations there is only a 98 millisecond gain.  Less than 1 second.

So, *is* there a real difference of one method over the other?

S.

Post #295740
 Posted Thursday, July 20, 2006 12:28 AM
 Say Hey Kid Group: General Forum Members Last Login: Friday, September 06, 2013 2:16 AM Points: 693, Visits: 124

Nice work, R2ro! It seems that your function is the best, so far. Here are some timings (in ms) for the three versions (counting the rows in the result, for the dates between Jan 1, 2006 and Jan 31, 2307):

 Peter Razvan R2ro 1906 1670 766 2076 2846 796 830 1653 876 906 610 766 860 1703 873 3170 2846 703

As you can see, on my system, the timings are varying strangely, but on average, your method seems to be the best.

Razvan

Post #295786
 Posted Thursday, July 20, 2006 12:47 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 6:43 AM Points: 2,378, Visits: 3,350
 This is an interesting approach!However, since float is not exact value there might be a difference in result. But since we only want to keep day information that might not be a problem.If you have the time, run your test-code again with this codeselect cast(cast(getdate() as int) as datetime) N 56°04'39.16"E 12°55'05.25"
Post #295789
 Posted Thursday, July 20, 2006 2:05 AM
 Say Hey Kid Group: General Forum Members Last Login: Friday, September 06, 2013 2:16 AM Points: 693, Visits: 124
 Peter, the following code:select cast(cast(getdate() as int) as datetime)will give incorrect results if the time is in the afternoon. However, the code that Simon wrote:select cast(floor(cast(getdate() as float)) as datetime)gives the correct result.Razvan
Post #295799
 Posted Thursday, July 20, 2006 2:35 AM
 SSC-Addicted Group: General Forum Members Last Login: Friday, June 17, 2011 6:28 AM Points: 422, Visits: 33
 I believe the following function is faster than all the above suggested:CREATE FUNCTION dbo.fnSeqDates(    @LowDate DATETIME,    @HighDate DATETIME)RETURNS @Dates TABLE        (            SeqDate DATETIME        )ASBEGIN DECLARE @Temp DATETIME  DECLARE @NumberOfDays int IF @LowDate > @HighDate  SELECT @Temp = @LowDate,   @LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate) - 1, 0),   @HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0) ELSE         SELECT @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate) - 1, 0),   @HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0) SELECT @NumberOfDays = DATEDIFF(d, @LowDate, @HighDate) INSERT @Dates  SELECT DATEADD(d, Number, @LowDate) AS Date FROM Numbers WHERE Number <= @NumberOfDays RETURNENDGO  It uses a Numbers table, containing all Numbers less than, say 1000000. This can be created once and for all as follows:SELECT TOP 1000000 Number = IDENTITY(INT, 1, 1) INTO NumbersFROM    sysobjects a1  CROSS JOIN    sysobjects a2  CROSS JOIN    sysobjects a3  CROSS JOIN    sysobjects a4  CROSS JOIN    sysobjects a5ALTER TABLE Numbers        ADD CONSTRAINT Index_Numbers PRIMARY KEY CLUSTERED(Number)GO
Post #295804

 Permissions