hi,
Please help me in following code of stored procedure….
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
Thanks,
Deven Oza