while loop in sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

while loop in sql

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-14 : 02:49:40

I'm trying to display all dates between 01/05/2012 and 31/05/2012 using the following loop.

When I run this code, i get message "command successfully completed" but it doesn't display the resultset. Is there any mistake in the code?


declare @firstint datetime
declare @secondint datetime
set @firstint = 01/05/2012
set @secondint = 01/06/2012
while
@firstint < @secondint
begin
print @firstint
set @firstint = @firstint + 1
end

Rimsky
Starting Member

12 Posts

Posted - 2012-09-14 : 03:00:37

Hi,
You're assuming that the dateformat you entered is understood by SQL Server. First Try This:


DECLARE @firstint DateTime
DECLARE @secondint DateTime
SET @firstint	= 01/05/2012
SET @secondint	= 01/06/2012

PRINT @FirstInt
WHILE @firstint < @secondint
BEGIN
	PRINT @firstint
	SET @firstint = @firstint + 1
END

You'll see that the first date is interpreted as 01-01-1900

Then, try this one:


SET DATEFORMAT DMY

DECLARE @firstint DateTime
DECLARE @secondint DateTime
SET @firstint	= '01/05/2012'
SET @secondint	= '01/06/2012'

WHILE @firstint < @secondint
BEGIN
	PRINT @firstint
	SET @firstint = @firstint + 1
END

Succes

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-14 : 03:43:18

You should set a datetime variable always this way:
set @firstint = '20120501' -- YYYYMMDD

You should always use DATEADD() to add a day to a date:
SET @firstint = dateadd(day,1,@firstint)

Too old to Rock'n'Roll too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-14 : 07:32:41

And here is the reason why you need unambiguous date format
http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-14 : 09:21:19

Thanks Webfred Rimsky and Madhivanan.
Madhivanan and Webfred, you both have given very useful information for me.
@Rimsky
Thank you too but I have a question on this
In your first code, you have two print statements one before while and inside "while loop"
but in the second code you have only one statement.

Can I know the difference between them?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 10:21:10

quote:
Originally posted by learning_grsql

Thanks Webfred Rimsky and Madhivanan.
Madhivanan and Webfred, you both have given very useful information for me.
@Rimsky
Thank you too but I have a question on this
In your first code, you have two print statements one before while and inside "while loop"
but in the second code you have only one statement.

Can I know the difference between them?

Print statements are only for showing intermediate results there's no other relevance for it.

see below to understand issue

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

in first value sent by you for date is interpreted as integer value

ie 01/05/2012 is interpreted as 0 which is 1 divided by 5 divided by 2012

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources