select dateadd(dd, n.number, h.StartDate)
from master..spt_values n
	join PublicHols h on n.type = 'P' 
			  and dateadd(dd,n.number,StartDate) between h.StartDate and h.EndDate

--===== If test table exists, drop it
 IF OBJECT_ID('TempDB..PublicHols','U') IS NOT NULL
	DROP TABLE PublicHols

--===== Create test table 
 CREATE TABLE PublicHols 
        (
        ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	Descr CHAR(64),
        StartDate DATETIME,
	EndDate DATETIME
        )

--===== Special conditions 
 SET DATEFORMAT DMY

--===== Insert test data into test table
 INSERT INTO PublicHols (Descr,StartDate,EndDate)
 SELECT 'Fiestas Patrias','17/09/2007', '19/09/2007'
 UNION ALL
 SELECT 'Thanksgiving Break','22/11/2007', '23/11/2007'
 UNION ALL
 SELECT  'Australia Day','26/01/2010', '26/01/2010'
 UNION ALL
 SELECT  'Anzac Day','25/04/2010', '25/04/2010'
 UNION ALL
 SELECT  'Christmas Break','25/12/2010', '26/12/2010'

--==== Gather the data
select 
	h.ID,
	h.Descr,
	h.StartDate,
	h.EndDate,
	cast(h.EndDate-h.StartDate as integer)+1 as Days
from PublicHols h 

--==== One solution to the problem (from another forum)
select 
	h.ID,
	h.Descr,
	dateadd(dd, n.number, h.StartDate) as HolDate,
	1 as Days
from master..spt_values n
	join PublicHols h on n.type = 'P' 
			  and dateadd(dd,n.number,h.StartDate) between h.StartDate and h.EndDate

;WITH cteTally (N) AS
  (
   SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) 
   FROM master..syscolumns
  )
   SELECT P.ID, P.Descr, Z.HolDate, 1 AS Days
   FROM PublicHols AS P
   CROSS APPLY
    (
     SELECT DATEADD(DAY, N, StartDate) - 1
     FROM cteTally
     WHERE N < DATEDIFF(DAY, StartDate, EndDate) + 2
    ) AS Z (HolDate)
   ORDER BY HolDate