Solved

insert rows into temp table

Posted on 2013-01-14
6
410 Views
Last Modified: 2013-01-15
im trying to insert rows into a temp table but am not having any luck.


declare @StartDate as date
declare @EndDate as date
declare @today as date
set @StartDate = '1/14/2013 2:18:28 PM'
set @EndDate = '1/14/2013 2:18:28 PM'
set @today = getdate()
declare @myCounter as int
set @myCounter = 0

--drop table #tempDates

CREATE TABLE #tempDates(myDate date NULL)

if(@today != @StartDate)
begin
      --when today doesn't equal to startdate
      --ex: today 1/14 and start date 1/20
      -- should be 1/20, 1/21, 1/22, 1/23, 1/24, 1/25, 1/27
      while (@myCounter <= 7)
      begin
            set @StartDate = dateadd(dd, 1, @StartDate)
            insert into #tempDates values (@StartDate)
      end
end
else
begin
      --when today does equal startdate
      --ex: today 1/14 and start date 1/14
      -- should be 1/14, 1/15, 1/16, 1/17, 1/18, 1/19, 1/20
      while (@myCounter <= 7)
      begin
            set @StartDate = dateadd(dd, 1, @StartDate)
            insert into #tempDates values (@StartDate)
      end
end

select * from #tempDates
0
Comment
Question by:fwstealer
  • 3
  • 3
6 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38776346
Works fine for me, atlhough of course the dates start at 1/21 since the day is added before it goes to the temp table.
0
 

Author Comment

by:fwstealer
ID: 38776397
i can't get it to work at all; need the dates to begin at the startdate so how is that corrected.

when i run it - it just spins and never returns anything
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 38776596
Oh, I corrected the no-return part: sorry, based on your comments about the results returned, I assumed you had to.  Here it is:


declare @StartDate as date
declare @EndDate as date
declare @today as date
set @StartDate = '1/14/2013 2:18:28 PM'
set @EndDate = '1/14/2013 2:18:28 PM'
set @today = getdate()
declare @myCounter as int
set @myCounter = 0

--drop table #tempDates

CREATE TABLE #tempDates(myDate date NULL)

if(@today != @StartDate)
begin
      --when today doesn't equal to startdate
      --ex: today 1/14 and start date 1/20
      -- should be 1/20, 1/21, 1/22, 1/23, 1/24, 1/25, 1/27
      while (@myCounter <= 7)
      begin
            insert into #tempDates values (@StartDate)
            set @StartDate = dateadd(dd, 1, @StartDate)
            set @myCounter = @myCounter + 1  
      end
end
else
begin
      --when today does equal startdate
      --ex: today 1/14 and start date 1/14
      -- should be 1/14, 1/15, 1/16, 1/17, 1/18, 1/19, 1/20
      while (@myCounter <= 7)
      begin
            insert into #tempDates values (@StartDate)
            set @StartDate = dateadd(dd, 1, @StartDate)
            set @myCounter = @myCounter + 1
      end
end

select * from #tempDates
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:fwstealer
ID: 38778229
just noticed that the solution provided is producing 8 rows when i only need 7; what needs to change to get 7 rows?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38778774
Change:

while (@myCounter <= 7)

to

while (@myCounter < 7)

in both places.
0
 

Author Comment

by:fwstealer
ID: 38778787
thanks scott
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now