SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Change a CTE to use table variable
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

richardlaw
Yak Posting Veteran

United Kingdom
68 Posts

Posted - 05/04/2013 :  19:22:20  Show Profile  Visit richardlaw's Homepage  Reply with Quote
Hi

I've found the following code which I need to change from a CTE to a table variable. I'm new to SQL, so any help is very much appreciated.


declare @DateFrom DateTime
declare @DateTo DateTime

set @DateFrom ='2013-04-29'
set @DateTo = '2013-05-27'


;WITH CTE(classDate)
AS
(
      Select @DateFrom
      Union All
      Select DATEADD(d,1,classDate)FROM CTE
      Where classDate<@DateTo
)

select classDate
from CTE
where DATENAME(dw,classDate)In('Monday')


Thanks as always

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/04/2013 :  22:57:35  Show Profile  Reply with Quote
Here is a way:

declare @DateFrom Date
declare @DateTo Date

set @DateFrom ='2013-05-04'
set @DateTo = '2013-06-27'

CREATE TABLE #TMP(classDate Date)

INSERT INTO #TMP(classDate) 
SELECT AllDates from 
(Select DATEADD(d, number, @dateFrom) as AllDates from 
         master..spt_values where type = 'p' and number between 0 and 
          datediff(dd, @dateFrom, @dateTo)) AS D1
WHERE DATENAME(dw, D1.AllDates)In('Monday');

SELECT * FROM #TMP;

DROP TABLE #TMP;

     
Go to Top of Page

richardlaw
Yak Posting Veteran

United Kingdom
68 Posts

Posted - 05/05/2013 :  05:17:43  Show Profile  Visit richardlaw's Homepage  Reply with Quote
Perfect. Thanks.

quote:
Originally posted by MuMu88

Here is a way:

declare @DateFrom Date
declare @DateTo Date

set @DateFrom ='2013-05-04'
set @DateTo = '2013-06-27'

CREATE TABLE #TMP(classDate Date)

INSERT INTO #TMP(classDate) 
SELECT AllDates from 
(Select DATEADD(d, number, @dateFrom) as AllDates from 
         master..spt_values where type = 'p' and number between 0 and 
          datediff(dd, @dateFrom, @dateTo)) AS D1
WHERE DATENAME(dw, D1.AllDates)In('Monday');

SELECT * FROM #TMP;

DROP TABLE #TMP;

     


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000