September 1, 2009 at 12:54 am
Hi everyone,
This is my first post in sql server central i am not sure this is the correct forum for me .
I have a table containing in and out dates of employees.These dates contain saturdays and sundays.My requirement is to shift the dates from saturday and sunday to monday and same with out dates.I have written a SPas shown below
USE [test]
GO
/****** Object: StoredProcedure [dbo].[DisplayDays] Script Date: 08/25/2009 12:53:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[DisplayDays]
as
declare @indate smalldatetime
declare @outdate smalldatetime
declare @diff int
declare @presentidate smalldatetime
declare @presentodate smalldatetime
create table #temp(idate smalldatetime,odate smalldatetime)
set @indate='8/7/2009'
set @outdate='8/31/2009'
set @diff=datediff(dd,@indate,@outdate)
if (select datename(dw,@indate))='Sunday'
set @presentidate=dateadd(day,1,@indate)
else
begin
if(select datename(dw,@indate))='Saturday'
set @presentidate=dateadd(day,2,@indate)
else
set @presentidate=@indate
end
set @presentodate=@presentidate
while(@diff>0)
begin
set @presentodate=dateadd(day,1,@presentodate)
if(select datename(dw,@presentodate)) not in ('Sunday','Saturday')
set @diff=@diff-1
end
insert into #temp(idate ,odate) values(@presentidate,@presentodate)
select * from #temp
I have more than 1 lakh records and my SP is not executing.I need help to make it executable.
September 8, 2009 at 8:09 pm
You may follow the script similar to below for each column.
UPDATE TEST_DAY
SET IDATE=DATEADD(DD,1,IDATE)
WHERE datename(DW,idate)='sunday'
UPDATE TEST_DAY
SET IDATE=DATEADD(DD,2,IDATE)
WHERE datename(DW,idate)='saturday'
Swarndeep
http://talksql.blogspot.com
September 21, 2009 at 12:05 pm
Try this
UPDATE TEST_DAY
SET IDATE = CASE WHEN DATEPART(dw,getdate()) = 1
THEN DATEADD(DD,1,IDATE)
WHEN DATEPART(dw,getdate()) = 7
THEN DATEADD(DD,2,IDATE)
End
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply