need help!!

[Deven Oza]

Hello Friends,

Please help me in following code of stored procedure…. I think the problem is in between of BEGIN and END.


My table and data: select * from nextGen_deven
--------------------------------------------------------------------------
Name1 enterdate convertedDate
Deven 2008-01-05 00:00:00.000 NULL
Deven1 2008-02-05 10:10:10.000 NULL
Deven2 2008-03-05 10:20:10.000 NULL
Deven3 2008-04-05 22:22:00.000 NULL
Deven4 2008-05-05 22:22:00.000 NULL
Deven5 2008-06-05 22:22:00.000 NULL
Deven6 2008-07-05 22:22:00.000 NULL
Deven7 2008-08-05 22:22:00.000 NULL
Deven8 2008-09-05 22:22:00.000 NULL
Deven9 2008-10-05 22:22:00.000 NULL
Deven10 2008-11-05 22:22:00.000 NULL
Deven11 2008-12-05 22:22:00.000 NULL

I have written stored procedure to add 2 hours or 1 hour in table data.

create PROCEDURE aim_sp_Convertdate_Deven AS
declare @DLSStart smalldatetime
, @DLSEnd smalldatetime
, @Date smalldatetime

/* please note: following two functions return me ‘2008-03-09 02:00:00’ and ‘2008-11-02 02:00:00’ values

(select dbo.fn_GetDaylightSavingsTimeStart(convert(varchar ,datepart(year,getdate())))) = ‘2008-03-09 02:00:00’

(select dbo.fn_GetDaylightSavingsTimeEnd(convert(varchar,d atepart(year,getdate())))) = ‘2008-11-02 02:00:00’

*/

set @DLSStart = (select dbo.fn_GetDaylightSavingsTimeStart(convert(varchar ,datepart(year,getdate()))))

set @DLSEnd = (select dbo.fn_GetDaylightSavingsTimeEnd(convert(varchar,d atepart(year,getdate()))))


DECLARE date_cursor CURSOR FOR
(select enterdate from nextGen_deven)
OPEN date_cursor
FETCH NEXT FROM date_cursor into @Date
WHILE @@FETCH_STATUS = 0
BEGIN
begin
update NextGen_Deven
set convertedDate = dateadd(hour, 1, enterDate)
where @Date between @DLSStart and @DLSEnd
end
begin
update NextGen_Deven
set convertedDate = dateadd(hour, 2, enterDate)
where @Date not between @DLSStart and @DLSEnd
end
FETCH NEXT FROM date_cursor into @Date END
CLOSE date_cursor
DEALLOCATE date_cursor



When I execute this procedure, it updates every row by adding 2 hours (not 1 hour in some of row)

Begin Tran abc
exec aim_sp_Convertdate_Deven
select * from nextGen_deven

my updated table data
--------------------------------------------------------------------------
Name1 enterdate convertedDate
Deven 2008-01-05 00:00:00.000 2008-01-05 02:00:00.000
Deven1 2008-02-05 10:10:10.000 2008-02-05 12:10:10.000
Deven2 2008-03-05 10:20:10.000 2008-03-05 12:20:10.000
Deven3 2008-04-05 22:22:00.000 2008-04-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven4 2008-05-05 22:22:00.000 2008-05-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven5 2008-06-05 22:22:00.000 2008-06-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven6 2008-07-05 22:22:00.000 2008-07-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven7 2008-08-05 22:22:00.000 2008-08-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven8 2008-09-05 22:22:00.000 2008-09-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven9 2008-10-05 22:22:00.000 2008-10-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven10 2008-11-05 22:22:00.000 2008-11-06 00:22:00.000
Deven11 2008-12-05 22:22:00.000 2008-12-06 00:22:00.000



Thank you very much,
Deven

[Delerna]

Double post. See the other Post