Recherche toute les dates entre deux dates

Version imprimable

08/01/2010, 14h54
Oberown

Recherche toute les dates entre deux dates

Existe-t-il une fonction permettant de récuperer toute les dates entre deux dates ?

Par exemple: SELECT DateBetween('2007/02/25', '2007/03/02')

Et qui renvoit
2007/02/25
2007/02/26
2007/02/27
2007/02/28
2007/03/01
2007/03/02

Cela n'existe pas, tu dois crées des Number/Tally table.

Mais, comme je suis un mec cool, voici les miennes :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
-- 2009-12-07
CREATE VIEW dbo.v_VIRTUAL_NUMBERS
AS
WITH
L1 AS (SELECT 1 AS n UNION ALL SELECT 1) -- 2^1
, L2 AS (SELECT X.n FROM L1, L1 AS X) -- (2^1)^2 = 2^2
, L3 AS (SELECT X.n FROM L2, L2 AS X) -- (((2^1)^2)^2 = 2^4
, L4 AS (SELECT X.n FROM L3, L3 AS X) -- ((((2^1)^2)^2)^2 = 2^8
, L5 AS (SELECT X.n FROM L4, L4 AS X) -- (((((2^1)^2)^2)^2)^2 = 2^16
, L6 AS (SELECT X.n FROM L5, L5 AS X) -- ((((((2^1)^2)^2)^2)^2)^2 = 2^32
SELECT ROW_NUMBER() OVER (ORDER BY n) AS n FROM L6
GO
 
-- 2009-12-08
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[v_VIRTUAL_YEARS]'))
DROP VIEW [dbo].[v_VIRTUAL_YEARS]
GO
 
CREATE VIEW dbo.v_VIRTUAL_YEARS
AS
SELECT
	CAST(n AS INT) AS yyyy
	, DATEADD(yyyy, (n - 1900), '01-01-1900') AS [begin]
	, DATEADD(yyyy, (n + 1 - 1900), '01-01-1900') AS [end]
FROM dbo.v_VIRTUAL_NUMBERS
WHERE n BETWEEN 1900 AND 2078
GO
 
-- 2010-01-05
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[v_VIRTUAL_DAYS]'))
DROP VIEW [dbo].[v_VIRTUAL_DAYS]
GO
 
CREATE VIEW dbo.v_VIRTUAL_DAYS
AS
SELECT
	Y.yyyy AS yyyy
	, N.n - 1 AS dy
	, DATEADD(dd, N.n - 1, Y.[begin]) AS date
FROM (
	SELECT
		Y.*
		, DATEDIFF(dd, Y.[begin], Y.[end]) AS DDIF
	FROM dbo.v_VIRTUAL_YEARS AS Y
) AS Y
INNER LOOP JOIN dbo.v_VIRTUAL_NUMBERS AS N ON (
	N.n >= 1
	AND
	N.n <= 366
	AND
	N.n <= Y.DDIF
)
GO

Il ne te reste qu'à faire ton select

Code:

1
2
3
4
 
SELECT date
FROM dbo.v_VIRTUAL_DAYS
WHERE date between '2007-02-25' AND '2007-03-02'

Bonjour,

Une solution plus simple d'après moi consiste à utiliser une fonction SQL utilisant une CTE recursive (SQL Server 2008 vu le type date utilisé):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
CREATE FUNCTION [dbo].[DatesTable]
(
  @FirstDate date,
  @LastDate date
)
RETURNS @datetable TABLE (
  [date] date
)
AS
BEGIN
 
  WITH CTE_DatesTable
  AS
  (
    SELECT @FirstDate AS [date]
    UNION ALL
    SELECT DATEADD(dd, 1, [date])
    FROM CTE_DatesTable
    WHERE DATEADD(dd, 1, [date]) <= @LastDate
  )
  INSERT INTO @datetable ([date])
  SELECT [date] FROM CTE_DatesTable
  OPTION (MAXRECURSION 0);
 
  RETURN;
END

Pour son utilisation:

Code:

1
2
3
 
SELECT [date]
FROM [dbo].[DatesTable](GETDATE(), DATEADD(dd,5,GETDATE()))

Ici une version fonctionnant sous 2005:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE FUNCTION [dbo].[DateTable]
(
  @FirstDate datetime,
  @LastDate datetime
)
RETURNS @datetable TABLE (
  [date] datetime
)
AS
BEGIN
 
  SELECT @FirstDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @FirstDate));   SELECT @LastDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @LastDate)); 
  WITH CTE_DatesTable
  AS 
  (
    SELECT @FirstDate AS [date]
    UNION ALL
    SELECT DATEADD(dd, 1, [date])
    FROM CTE_DatesTable
    WHERE DATEADD(dd, 1, [date]) <= @LastDate
  )
  INSERT INTO @datetable ([date])
  SELECT [date] FROM CTE_DatesTable
  OPTION (MAXRECURSION 0)
 
  RETURN
END

Son utilisation est similaire à la fonction pour 2008.

Bonne journée

12/01/2010, 10h58
Waldar

Une table calendrier c'est encore mieux à mon avis.

SQLPro a écrit un cours sur un modèle complet :
http://sqlpro.developpez.com/cours/gestiontemps/