Problem in the SP - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem in the SP

Author

Topic

shm
Yak Posting Veteran

86 Posts

Posted - 2009-03-05 : 04:51:45

hi

In the SP
i am using the four parameter as

@frommonth-- 10
@fromyear-- 2008
@tomonth-- 01
@toyear-- 2009

like this the value i il pass

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = dateadd(mm,(@fromyear-1900)* 12 + @frommonth - 1,0) + (1-1)
SET @EndDate = dateadd(mm,(@toyear-1900)* 12 + @tomonth - 1,0) + (1-1)

select @StartDate
select @EndDate

WHILE ((month(@StartDate) <= month(@EndDate))
and (year(@StartDate) <= year(@EndDate)))
begin
.....

SET @StartDate = DATEADD(month,1,@StartDate)

end

if i give the frommonth=10 tomonth=12
and toyear =2008 fromyear=2008

the above condition il work

but if i give frommonth=10 and tomonth=01
and toyear =2008 fromyear=2009

i will get error becoz the first while condition il only fail
it should work in both the condition

how to change this condition

asgast
Posting Yak Master

149 Posts

Posted - 2009-03-05 : 05:10:07

tr while @startdate <= @enddate
if you formated the date correctly everthing should be ok

shm
Yak Posting Veteran

86 Posts

Posted - 2009-03-05 : 07:17:01

hi

no it is coming wrong now also

while passing the value am giving like this

@frommonth-- 10
@fromyear-- 2008
@tomonth-- 01
@toyear-- 2009

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-03-05 : 07:52:28

[code]declare @frommonth int, @fromyear int, @tomonth int, @toyear int
--set @frommonth = 10
--set @fromyear = 2008
--set @tomonth = 12
--set @toyear = 2008

set @frommonth = 10
set @fromyear = 2008
set @tomonth = 1
set @toyear = 2009

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = dateadd(mm,(@fromyear-1900)* 12 + @frommonth - 1,0) + (1-1)
SET @EndDate = dateadd(mm,(@toyear-1900)* 12 + @tomonth - 1,0) + (1-1)

select @StartDate
select @EndDate

--WHILE ((month(@StartDate) <= month(@EndDate))
--and (year(@StartDate) <= year(@EndDate)))
WHILE (year(@StartDate) = year(@EndDate) AND month(@StartDate) <= month(@EndDate))
OR
(year(@StartDate) < year(@EndDate))
begin

SET @StartDate = DATEADD(month,1,@StartDate)
select 'result: ', @StartDate

end
[/code]

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

asgast
Posting Yak Master

149 Posts

Posted - 2009-03-05 : 07:55:40

DECLARE @frommonth int,@fromyear int, @tomonth int, @toyear int

SET @frommonth = 10
SET @fromyear = 2008
SET @tomonth = 02
SET @toyear= 2009

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = dateadd(mm,(@fromyear-1900)* 12 + @frommonth - 1,0) + (1-1)
SET @EndDate = dateadd(mm,(@toyear-1900)* 12 + @tomonth - 1,0) + (1-1)

select @StartDate
select @EndDate

WHILE @StartDate < @EndDate begin

SET @StartDate = DATEADD(month,1,@StartDate)
print @startdate

end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-05 : 08:04:03

[code]DECLARE @FromMonth TINYINT,
@FromYear SMALLINT,
@ToMonth TINYINT
@ToYear SMALLINT

SELECT @FromMonth = 10,
@FromYear = 2008,
@ToMonth = 01,
@ToYear = 2009

DECLARE @StartDate DATETIME,
@EndDate DATETIME

SELECT @StartDate = DATEADD(MONTH, 12 * @FromYear - 22801 + @FromMonth, 0),
@EndDate = DATEADD(YEAR, 1, @StartDate)

WHILE @StartDate < @EndDate
BEGIN
...

SET @StartDate = DATEADD(MONTH, 1, @StartDate)
END[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-05 : 13:09:33

quote:
Originally posted by shm

hi

In the SP
i am using the four parameter as

@frommonth-- 10
@fromyear-- 2008
@tomonth-- 01
@toyear-- 2009

like this the value i il pass

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate =dateadd(mm,@frommonth-1,dateadd(yy,@fromyear-1900,0))
SET @EndDate = dateadd(mm,@tomonth-1,dateadd(yy,@toyear-1900,0))

select @StartDate
select @EndDate

WHILE @StartDate <= @EndDate
begin
.....

SET @StartDate = DATEADD(month,1,@StartDate)

end

if i give the frommonth=10 tomonth=12
and toyear =2008 fromyear=2008

the above condition il work

but if i give frommonth=10 and tomonth=01
and toyear =2008 fromyear=2009

i will get error becoz the first while condition il only fail
it should work in both the condition

how to change this condition

modify like above
also, i'm not sure why you need loop here

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources