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 2008 Forums
 Transact-SQL (2008)
 given date - bring back pay period end date

Author  Topic 

jobsinger
Starting Member

2 Posts

Posted - 2014-11-18 : 20:50:29
Hi - I'm new here.

I have a given date and I would like to get the biweekly end date.
Biweekly periods run from Sunday to Saturday.

For example:
My date is 01/03/2014
Should return biweekly end date as 01/04/2014

My date is 01/06/2014
Should return biweekly end date as 01/18/2014

I figured out how to get the week ending date, but can't get it to do a biweekly date.

TO GET WEEK END DATE:

dateadd(day, -1 * datepart(weekday, My date here) + 7 WEEKEND_DATE

Any help is much appreciated!
Thank you.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-11-19 : 01:20:09
Hi,

My first idee is this:

declare @dDate1 as DATE = '20140103'
,@dDate2 as DATE ='20140106'
,@dDate AS DATE

SET @dDate = @dDate1

SELECT

CASE WHEN DATEPART(wk,@dDate) % 2 = 0 THEN DATEADD(d,5,DATEADD(wk,1,DATEADD(wk,DATEDIFF(wk,0,@dDate),0)))
ELSE DATEADD(d,5,DATEADD(wk,DATEDIFF(wk,0,@dDate),0)) END





sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-11-19 : 01:25:55
You can have a calendar table and have an additional column <biWeeklyDate> - and base on your given date to extract the desired value
Or create a scalar function that return the desired value



;WITH aCTE
AS
(
SELECT CAST('20140101'AS DATETIME) AS myDate ,CAST('20140104' AS DATETIME) AS biWeeklyDate

UNION ALL

SELECT
DATEADD(d,1,myDate), CASE WHEN DATEPART(wk,myDate) % 2 = 0 THEN DATEADD(d,5,DATEADD(wk,1,DATEADD(wk,DATEDIFF(wk,0,myDate),0)))
ELSE DATEADD(d,5,DATEADD(wk,DATEDIFF(wk,0,myDate),0)) END
FROM
aCTE
WHERE
myDate<'20140131')

SELECT * FROM aCTE



sabinWeb MCP
Go to Top of Page

jobsinger
Starting Member

2 Posts

Posted - 2014-11-19 : 18:09:12
Thank you these were very helpful!
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-11-20 : 12:19:39
I'd strongly suggest avoiding any code with dependencies on @@DATEFIRST and/or language settings.

How about the code below instead, which works with any date/language settings. Btw, "5" is not a "magic" date, it's simply a known, earlier Saturday (Jan 6 1900) that serves as a "base" date for calcs.

SELECT
my_date,
DATEADD(DAY, CEILING(DATEDIFF(DAY, 5, my_date) / 14.0) * 14, 5) AS ending_pay_date
FROM (
SELECT CAST('01/03/2014' AS datetime) AS my_date UNION ALL
SELECT '01/04/2014' UNION ALL
SELECT '01/06/2014' UNION ALL
SELECT '01/18/2014' UNION ALL
SELECT '01/19/2014'
) AS my_dates
Go to Top of Page
   

- Advertisement -