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

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.

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)

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

cast(floor(cast(getdate() as float)) as datetime)

iterations	datediff	floor
1	0	0
10	0	0
100	0	0
1000	7	6
10000	68	75
100000	712	810

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):

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

Razvan

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)

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

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