Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

How many more Mondays until I retire? Expand / Collapse
Author
Message
Posted Wednesday, July 19, 2006 8:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:24 AM
Points: 945, Visits: 260
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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399

The best way would be to use this function

CREATE 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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 10, 2010 7:15 AM
Points: 18, Visits: 18

I had the idea of populating the Holiday table myself this morning. I found a function to calculate the date for Easter on this page:

http://www.databasejournal.com/scripts/article.php/3469911

Then I used that along with Peter's seqDates function to create the following SP. Covers all UK Holidays, and if New Year, Christmas or Boxing Day occur at the weekend it inserts the appropriate Monday/Tuesday into the table.

CREATE PROCEDURE GenerateHolidays (@yr SMALLINT)

AS

DECLARE @tmpdatec VARCHAR(10)
DECLARE @tmpdate SMALLDATETIME
DECLARE @tmpdesc VARCHAR(50)
DECLARE @sdate   VARCHAR(10)
DECLARE @edate   VARCHAR(10)

SET @tmpdatec = '01/01/' + CAST(@yr AS VARCHAR)
SET @tmpdate = CAST(@tmpdatec AS SMALLDATETIME)
IF DATEPART(dw, @tmpdate) = 7 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate)
IF DATEPART(dw, @tmpdate) = 1 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate)
SET @tmpdesc = 'New Year''s Day'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SELECT @tmpdate = dbo.fnYear2Easter(@yr)
SELECT @tmpdate = DATEADD(DAY, -2, @tmpdate)
SELECT @tmpdesc = 'Good Friday'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SELECT @tmpdate = DATEADD(DAY, 3, @tmpdate)
SELECT @tmpdesc = 'Easter Monday'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SET @sdate = '05/01/' + CAST(@yr AS VARCHAR)
SET @edate = '05/31/' + CAST(@yr AS VARCHAR)

SELECT @tmpdate = MIN(dt.SeqDate) FROM dbo.fnSeqDates(@sdate, @edate) dt WHERE DATEPART(dw, dt.SeqDate) = 2
SET @tmpdesc = 'May Day Bank Holiday'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SELECT @tmpdate = MAX(dt.SeqDate) FROM dbo.fnSeqDates(@sdate, @edate) dt WHERE DATEPART(dw, dt.SeqDate) = 2
SET @tmpdesc = 'Spring Bank Holiday'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SET @sdate = '08/01/' + CAST(@yr AS VARCHAR)
SET @edate = '08/31/' + CAST(@yr AS VARCHAR)

SELECT @tmpdate = MAX(dt.SeqDate) FROM dbo.fnSeqDates(@sdate, @edate) dt WHERE DATEPART(dw, dt.SeqDate) = 2
SET @tmpdesc = 'August Bank Holiday'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SET @tmpdatec = '12/25/' + CAST(@yr AS VARCHAR)
SET @tmpdate = CAST(@tmpdatec AS SMALLDATETIME)
IF DATEPART(dw, @tmpdate) = 7 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate)
IF DATEPART(dw, @tmpdate) = 1 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate)
SET @tmpdesc = 'Christmas Day'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SET @tmpdatec = '12/26/' + CAST(@yr AS VARCHAR)
SET @tmpdate = CAST(@tmpdatec AS SMALLDATETIME)
IF DATEPART(dw, @tmpdate) = 7 SELECT @tmpdate = DATEADD(DAY, 2, @tmpdate)
IF DATEPART(dw, @tmpdate) = 1 SELECT @tmpdate = DATEADD(DAY, 2, @tmpdate)
SET @tmpdesc = 'Boxing Day'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)


 

Post #295557
Posted Wednesday, July 19, 2006 12:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 7, 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

   RETURN
END
go

Post #295627
Posted Wednesday, July 19, 2006 6:54 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:19 PM
Points: 605, Visits: 1,691

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:
iterationsdatedifffloor
100
1000
10000
100076
100006875
100000712810
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

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 6, 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):

PeterRazvanR2ro
19061670

766

20762846796
8301653876
906610766
8601703873
31702846703

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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399

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 code

select 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

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 6, 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

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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
        )
AS
BEGIN

 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

 RETURN
END
GO

 

 

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 Numbers
FROM
    sysobjects a1
  CROSS JOIN
    sysobjects a2
  CROSS JOIN
    sysobjects a3
  CROSS JOIN
    sysobjects a4
  CROSS JOIN
    sysobjects a5


ALTER TABLE Numbers
        ADD CONSTRAINT Index_Numbers PRIMARY KEY CLUSTERED(Number)
GO

Post #295804
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse