Loop to look through Dates
-
I have this loop here, but it doesnt seem to do what its meant to do, instead it loops all the way through to heaven knows;
Declare @End_Date VARCHAR(30),
@Start_Date VARCHAR(30),
@Error int,
@SQL varchar(3000),
@TableName varchar(100)
Set @Error = 0
SeLECt @Start_Date = CONVERT(VARCHAR(30),getdate()-6,113)
SeLECt @Start_Date
print CONVERT(VARCHAR(30),getdate(),113)
while @Start_Date <= CONVERT(VARCHAR(30),getdate(),113)
print 'boy'
select @Start_Date = dateadd(dd,1,@Start_Date)
print @Start_Date
-
There are 2 issues
1. You are using varchar for comparision
2. THere is no begin and end. In that case only first line will execute for the loop
WHILE
Datediff(dd,@Start_Date,CONVERT(VARCHAR(30),getdate(),113)) >= 0
BEGIN
print 'boy'
select @Start_Date = dateadd(dd,1,@Start_Date)
print @Start_Date
END
Kindest Regards,Amit Lohia
-
I will second Amit with the varchar comparison. We just ran into a problem with some vendors code where it converted an int into a varchar and did comparison on it. The problem is that as the number increased, the numbers represented as varchars failed in the app. A 999 and 1000 represented as varchar the 999 is greater than it. Your going to run into the same problem with dates represented as varchars. It would be alphabetic rather than date based.
Make sense?
Tom
-
John, to be clear on the suggestions from Amit and Tom, the danger of converting values before comparing them is not limited to int's. Dates face the same issue. Consider this list of dates, which is in ascending order in your code:
'01 Aug 2006 12:00:00.000'
'01 Dec 2006 12:00:00.000'
'01 Nov 2006 12:00:00.000'
'01 Oct 2006 12:00:00.000'
'30 Nov 2006 12:00:00.000'
'31 Aug 2006 12:00:00.000'
'31 Dec 2006 12:00:00.000'
'31 Oct 2006 12:00:00.000'
It is best to compare dates to dates. If you absolutely must convert to varchar, be sure to use a format that preserves the sort order, such as 121 (yyyy-mm-dd hh:mm:ss.mmm).
-
As a clarification, I used int to varchar as an example. But the same would happen with your dates august would be less than July if you convert dates to varchar. But as a datetime august is greater than july. Just have to be carefull when converting to different types and comparing the new values. Not always the easiest to figure out when 2005-2-1 > 2005-11-1.
Tom
-
A far better question would be what are you going to replace "PRINT 'BOY'" with and why do you think you need a loop instead of a setbased solution. Perhaps if we knew more about it, eh?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I think Jeff brings a important point. Post as what you tend to do in the loop. If it is more than Print "Boy"
Kindest Regards,Amit Lohia
-
I know that, John... what are the statements? You may not need the loop at all! Loops are a form of RBAR and generally defeat the power of an RDBMS!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
John,
Despite the fact that Joe has, once again, demonstrated that soft-skills are not his main focus in life
, he is absolutely correct... you asked us to help you write a loop... some of us know that loops are nasty little time consuming critters that should usually not be permitted anywhere near SQL or an RDBMS... tell us the rest of the story about what you're trying to do and maybe we can show you how to write some really high performance code...By the way, Joe... how the heck are ya? Long time no see...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Cool... got titles and synops for the new books that you might care to share... nothing like being on the "bleeding edge", ya know
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Very nice, Joe... when do they hit the streets?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hello Everyone,
This is what i am trying to do, I hvae Daily tables that are creaed everyday.
So, once i want to do some maintainnance on the database, I want to work with the last say 10 days,
Thats the whole point of using a cursor.
so If i want to work with talbles in the last 10 days
Set @startdate = 'A start DAte'
Set @ENDdate = 'An END DAte'
while startdate <= Enddate
sp_recompile 'DailyTable'+convert(varchar(30),@startdate,112)
Increment @startdate
You understand it ?
-
Yep, I understand what you are doing... I just don't know why...
sp_Recompile 'tablename' will (from Books Online) recompile the stored procedures and triggers that use the object to be recompiled the next time they are executed. First, I'm hoping you don't have triggers on any of these tables... second, I hope you don't have individually named stored procedures just for one table and another set for another table, etc...
Since you have tables named by date, I suspect that you have some dynamic SQL involved somewhere which will cause the stored procedures to recompile, anyway. Even if you don't have dynamic SQL, an execution plan is only retained in the server until something else needs the space. And, if the content of the table changes by what is considered to be a small bit, it's going to force a recompile, anyway.
In other words, your loop isn't buying you anything. Sure, the loop works, but the procs are going to recompile everytime, anyway.
If you want to continue to use the loop, you'll probably need to add "EXEC" in the same line and just before sp_Recompile... Since you're changing table names, stored procedures tend to not like calculations passed in as parameters so you'll need to do the calculation in a variable and then pass the variable.
Knowing (from a parallel post) that your tables have up to 10 million records each seems to justify the requirement for date-named tables, but I'm not so sure about that. I never had to resort to that in the two call accounting packages I made in SQL about a million years ago... I kept all the rated CDR's in a 90 day table for "online" access... the non-rated CDRs were kept in a similar but separate table. As the calls aged past 90 days, I'd sweep them into a "near-line" archive table for recovery to be able to answer questions for the PUC, FBI, and other government agencies. Those have a lifetime of 1 year. As those aged, they'd be swept from the archive onto long term tape or optical storage platters.
Since my 3rd party vendors required monthly files, I'd just select a month's worth of CDR's from the online table, mark them as "shipped" with the file name, and spool them out to a file for the vendors.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply