January 22, 2004 at 12:58 pm
Hi
I want to create a function which calculates the pay period. The pay period (every 2 weeks) for this year is 1/10/04, 1/24/04 and so on. When I give a date it should give me the next pay date and the number of days left for the pay, that is the difference between the given date and the pay date.
Thanks,
M
January 22, 2004 at 1:43 pm
create function paydate (@first_paydate datetime, @given_date datetime)
returns datetime
as
begin
declare @next_paydate datetime
select @next_paydate = dateadd(day, 14, @first_paydate)
while 1=1
begin
if datediff(day, @next_paydate, @given_date) > 0
select @next_paydate = dateadd(day, 14, @next_paydate)
else
break
end
return(@next_paydate)
end
SELECT dbo.paydate('2004-01-02', '2004-02-20') AS 'Next_PayDate'
------------------------------------------------------------------------
create function DayLeft (@first_paydate datetime, @given_date datetime)
returns int
as
begin
declare @date_left int
declare @next_paydate datetime
select @next_paydate = dateadd(day, 14, @first_paydate)
while 1=1
begin
if datediff(day, @next_paydate, @given_date) > 0
select @next_paydate = dateadd(day, 14, @next_paydate)
else
break
end
SELECT @date_left = DATEDIFF(day, @given_date, @next_paydate)
return (@date_left)
end
SELECT dbo.dayleft('2004-01-02', '2004-02-20') AS 'DayLeft'
January 23, 2004 at 6:39 am
Thankyou Allen for your Solution.
Actually I want to pass only one parameter to the function which is the current date or what ever date the user gives and the function needs to calculate the paydate. I am thinking like if we make the first paydate as the standard date and from there can we calculate the paydate.
for example: if I give 10/1/2004 as the first payday then it should calculate the paydate when current date is passed i.e, if I pass 9/2/2004 it should give the paydate as 9/4/2004.
Thanks
M
January 23, 2004 at 7:55 am
To get two values from one UDF, return a table:
CREATE FUNCTION dbo.u_DaysAndPayDate(@fromdate datetime)
RETURNS @t TABLE(Days tinyint, PayDate datetime) BEGIN
DECLARE @d tinyint
SET @d = 14 - DATEDIFF(d,'20031227',@fromdate) % 14
SET @d = CASE @d WHEN 14 THEN 0 ELSE @d END
INSERT @t
SELECT @d, @fromdate + @d RETURN END
I'd just use one scalar valued UDF, though, and compute the other value; e.g.:
CREATE FUNCTION dbo.u_DaysToPayDate(@fromdate datetime) RETURNS tinyint BEGIN
DECLARE @d tinyint
SET @d = 14 - DATEDIFF(d,'20031227',@fromdate) % 14
SET @d = CASE @d WHEN 14 THEN 0 ELSE @d END
RETURN @d END
DECLARE @date datetime
SET @date = '20040902'
SELECT *
FROM dbo.u_DaysAndPayDate(@date)
SELECT dbo.u_DaysToPayDate(@date), @date + dbo.u_DaysToPayDate(@date)
--Jonathan
January 23, 2004 at 9:33 am
It works, thank you for your help Jonathan.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply