create table #temp
(
pName Varchar(20),
DateBegin DateTime,
DateEnd DateTime
)
Insert Into #temp(pName, DateBegin, DateEnd)
Values('Player1', '01/04/2012', '01/05/2012')
Insert Into #temp(pName, DateBegin, DateEnd)
Values('Player2', '02/01/2012', '02/05/2012')
With DateRange(dt) As
(
Select Convert(Datetime, '01/01/2012')
UNion All
Select DateAdd(dd, 1, Dat.dt) From DateRange Dat Where Dat.dt < CONVERT(Datetime, '01/31/2012')
)
Select T.pName, Dt.dt from #temp T
Inner Join DateRange Dt on Dt.dt BETWEEN T.DateBegin and T.DateEnd
Drop Table #temp
Issue is with this following code line
With DateRange(dt) As
It shows following error message
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
I had the same issue with SQL server 2017. Use semicolon before the WITH statement as follows.
;WITH
Add some semicolons:
create table #temp
(
pName Varchar(20),
DateBegin DateTime,
DateEnd DateTime
)
Insert Into #temp(pName, DateBegin, DateEnd)
Values('Player1', '01/04/2012', '01/05/2012')
Insert Into #temp(pName, DateBegin, DateEnd)
Values('Player2', '02/01/2012', '02/05/2012');
With DateRange(dt) As
(
Select Convert(Datetime, '01/01/2012')
UNion All
Select DateAdd(dd, 1, Dat.dt) From DateRange Dat Where Dat.dt < CONVERT(Datetime, '01/31/2012')
)
Select T.pName, Dt.dt from #temp T
Inner Join DateRange Dt on Dt.dt BETWEEN T.DateBegin and T.DateEnd;
Drop Table #temp
http://sqlfiddle.com/#!6/06e89
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With