given date - bring back pay period end date

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

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

jobsinger
Starting Member

2 Posts

Posted - 2014-11-19 : 18:09:12

Thank you these were very helpful!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources