Prev: Linked server query error - how do I trace it?
Next: Enable XP_CmdShell
From: Racsus on 29 Jul 2010 04:42
Hi, how can I get number of monday, tuesday,... between 2 dates?

Thanks,
From: Erland Sommarskog on 29 Jul 2010 05:48
Racsus (Racsus(a)discussions.microsoft.com) writes:
> Hi, how can I get number of monday, tuesday,... between 2 dates?

DECLARE @offset int,
@day varchar(10)
SELECT @day = 'Tuesday'
SELECT @offset = CASE @day
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Mittwoch' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6
WHEN 'Sunday' THEN 0
END
SELECT datediff(WEEK, dateadd(DAY, -@offset, '20100726'),
dateadd(DAY, -@offset, '20100729'))

The idea here is that datediff counts boundary passages, and in case of
WEEK, this is always the passage from Saturday to Sunday, no matter the
setting of DATEFIRST. Thus, we can find the answer by moving the interval,
so that the weekday we are looking for becomes Sunday. Or Saturday,
depending on how you want to deal with if one of the dates in your interval
is a Tuesday or whatever you are looking for.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Henrik Staun Poulsen on 30 Jul 2010 08:54
On Jul 29, 10:42 am, Racsus <Rac...(a)discussions.microsoft.com> wrote:
> Hi, how can I get number of monday, tuesday,... between 2 dates?
>
> Thanks,

Hi Racsus,

May I suggest that you create a calendar table on your database?
You could use this link as an inspiration: http://www.sqlservercentral.com/articles/T-SQL/70482/

HIH
Henrik Staun Poulsen
www.stovi.com
From: hayko98 on 6 Aug 2010 14:09
On Jul 30, 5:54 am, Henrik Staun Poulsen <h...(a)stovi.com> wrote:
> On Jul 29, 10:42 am, Racsus <Rac...(a)discussions.microsoft.com> wrote:
>
> > Hi, how can I get number of monday, tuesday,... between 2 dates?
>
> > Thanks,
>
> Hi Racsus,
>
> May I suggest that you create a calendar table on your database?
> You could use this link as an inspiration:http://www.sqlservercentral.com/articles/T-SQL/70482/
>
> HIH
> Henrik Staun Poulsenwww.stovi.com

Or you can use this:
WITH CTE_DatesTable
AS
(
SELECT CAST('20100601' as datetime) AS [date]
UNION ALL
SELECT DATEADD(dd, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [date]) <= '20100630'
)

SELECT DATEPART(dw, date) AS [DW],COUNT(DISTINCT DATEADD(DAY,
DATEDIFF(DAY, 0 , date), 0)) AS [CNT]

FROM
CTE_DatesTable

GROUP BY DATEPART(dw, date)
OPTION (MAXRECURSION 0);

DW CNT
1 4
2 4 ----there are 4 Mondays in between '20100601' and '20100630'
3 5 ----there are 5 Tuesdays in between '20100601' and '20100630'
4 5
5 4
6 4
7 4
 | 
Pages: 1
Prev: Linked server query error - how do I trace it?
Next: Enable XP_CmdShell