Need Working all dates - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Need Working all dates

Author

Topic

Mageshkumar
Starting Member

29 Posts

Posted - 2008-07-18 : 08:50:59

Hi i need between two dates what are the working days are there like...,

In 2nd and 4th saturday workdays=5
and odd saturday workdays=6

Suppose if i give 7/2/2008 to 7/26/2008...,

Then I want these dates:

7/2/2008
7/3/2008
7/4/2008
7/5/2008
7/7/2008
7/8/2008
7/9/2008
7/10/2008
7/11/2008
7/14/2008
7/15/2008
7/16/2008
7/17/2008
7/18/2008
7/19/2008
7/18/2008
7/21/2008
7/22/2008
7/23/2008
7/24/2008
7/25/2008
7/26/2008

Any one plz help me regarding this...........,

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-18 : 09:20:46

Hi chk this...

DECLARE @StartDate DATETIME,
@EndDate DATETIME
SELECT @StartDate = '01-june-2008',
@EndDate = '30-june-2008'
;WITH DATE (Date1)
AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', @StartDate), '19000101')
UNION ALL
SELECT DATEADD(DAY, 1, Date1)
FROM DATE
WHERE Date1 < @EndDate
)
SELECT
CONVERT(VARCHAR(15),d1.DATE1 ,110) as [Working Date],
DATENAME(weekday, d1.Date1) [Working Day]
from DATE d1 where (DATENAME(weekday, d1.Date1)) not in ('Saturday','Sunday')

Mageshkumar
Starting Member

29 Posts

Posted - 2008-07-18 : 09:26:11

Hi thanks for ur reply...,

But when ever odd saturday will come it mean 1st or 3rd or 5th saturday...,

Those dates also i need

Plz help me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-18 : 09:31:26

Why don't you remove the saturday from the in part?
Or use MVJ's excellent function F_TABLE_DATE?

E 12°55'05.25"
N 56°04'39.16"

Mageshkumar
Starting Member

29 Posts

Posted - 2008-07-18 : 09:32:50

If i remove saturday from that place...,

Then it will give all the saturday dates too...,

But i dont want there 2nd and 4th saturday dates

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-18 : 10:06:44

[code]DECLARE @StartDate DATETIME,
@EndDate DATETIME

SELECT @StartDate = '01-july-2008',
@EndDate = '30-july-2008'

;WITH DATE(Date1)
AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', @StartDate), '19000101')

UNION ALL

SELECT DATEADD(DAY, 1, Date1)
FROM DATE
WHERE Date1 < @EndDate
)

SELECT [Working Date],
[Working Day]
FROM (
SELECT CONVERT(VARCHAR(15), d1.DATE1, 110) AS [Working Date],
DATENAME(weekday, d1.Date1) AS [Working Day],
ROW_NUMBER() OVER (PARTITION BY DATENAME(weekday, d1.Date1) ORDER BY d1.DATE1) AS RecID,
date1
FROM DATE AS d1
) AS d
WHERE [Working Day] IN ('monday', 'tuesday', 'wednesday', 'thursday', 'friday')
or [Working Day] = 'saturday' and recid % 2 = 1
order by date1[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources