Differences

This shows you the differences between two versions of the page.

--- creating_date_ranges [2018/05/13 16:58] – created david
+++ creating_date_ranges [2025/03/27 15:42] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
+====== SQL Server ======
 <code sql>
 WITH date_range AS (
@@ Line 22: / Line 23: @@
                    AND DateAdd(DAY,  DateDiff(DAY,   0, GetDate()), 0)  -- Today
 ORDER BY TheDate DESC
+;
+WITH date_list AS (
+SELECT DateAdd(DAY,
+               -ROW_NUMBER() OVER (ORDER BY (SELECT 100)),
+               DateAdd(day,   DateDiff(day,   0, GetDate()), 0)) AS TheDate
+  FROM sys.all_objects
+)
+SELECT TheDate
+  FROM date_list
+ WHERE TheDate BETWEEN '02-FEB-17'
+                   AND '26-MAR-18'
+ ORDER BY TheDate
+;
+</code>
+====== Oracle ======
+<code sql>
+WITH date_list AS (
+  SELECT TRUNC(SYSDATE) - rownum proc_date
+    FROM dual
+ CONNECT BY LEVEL < 365
+)
+  SELECT proc_date
+    FROM date_list
+ORDER BY proc_date
 ;
-      WITH date_list AS (
+FUNCTION get_date_range
-        SELECT TRUNC(SYSDATE) - rownum proc_date
+        (process_id IN NUMBER
-          FROM dual
+        ) RETURN SYS_REFCURSOR
-       CONNECT BY LEVEL < 365
+IS
-      )
+  c SYS_REFCURSOR;
-      SELECT proc_date
+BEGIN
-        FROM date_list
+  OPEN c FOR
-    ORDER BY proc_date
+    WITH date_list AS (
-    ;
+      SELECT TRUNC(SYSDATE) - rownum proc_date
+        FROM dual
+     CONNECT BY LEVEL < 365
-  FUNCTION get_date_range
+    )
-          (process_id IN NUMBER
+    SELECT proc_date
-          ) RETURN SYS_REFCURSOR
+      FROM date_list
-  IS
+  ORDER BY proc_date
-    c SYS_REFCURSOR;
+  ;
-  BEGIN
-    OPEN c FOR
+  RETURN c;
-      WITH date_list AS (
+END;
-        SELECT TRUNC(SYSDATE) - rownum proc_date
-          FROM dual
-       CONNECT BY LEVEL < 365
-      )
-      SELECT proc_date
-        FROM date_list
-    ORDER BY proc_date
-    ;
-    RETURN c;
-  END;
 -- Past
-      WITH date_list AS (
+  WITH date_list AS (
-        SELECT TRUNC(SYSDATE) - rownum proc_date
+    SELECT TRUNC(SYSDATE) - rownum proc_date
-          FROM dual
+      FROM dual
-       CONNECT BY LEVEL < 366 -- How many days to go back
+   CONNECT BY LEVEL < 366 -- How many days to go back
-      )
+  )
-      SELECT proc_date
+  SELECT proc_date
-        FROM date_list
+    FROM date_list
-    ORDER BY proc_date
+ORDER BY proc_date
-    ;
+;
 -- Future
-      WITH date_list AS (
+  WITH date_list AS (
-        SELECT TRUNC(SYSDATE) + rownum proc_date
+    SELECT TRUNC(SYSDATE) + rownum proc_date
-          FROM dual
+      FROM dual
-       CONNECT BY LEVEL < 366 -- How many days to go forward
+   CONNECT BY LEVEL < 366 -- How many days to go forward
-      )
+  )
-      SELECT proc_date
+  SELECT proc_date
-        FROM date_list
+    FROM date_list
-    ORDER BY proc_date
+ORDER BY proc_date
-    ;
+;
 -- Range
-    WITH date_list AS (
+WITH date_list AS (
-        SELECT TRUNC(SYSDATE) - rownum proc_date
+    SELECT TRUNC(SYSDATE) - rownum proc_date
-          FROM dual
+      FROM dual
-       CONNECT BY LEVEL < 1000
+   CONNECT BY LEVEL < 1000
-      )
+  )
-      SELECT proc_date
+  SELECT proc_date
-        FROM date_list
+    FROM date_list
-       WHERE proc_date BETWEEN '02-FEB-17'
+   WHERE proc_date BETWEEN '02-FEB-17'
-                           AND '26-MAR-18'
+                       AND '26-MAR-18'
-    ORDER BY proc_date
+ORDER BY proc_date
-    ;
+;
 -- Range (past)
-    WITH date_list AS (
+WITH date_list AS (
-        SELECT TRUNC(SYSDATE) - rownum proc_date
+    SELECT TRUNC(SYSDATE) - rownum proc_date
-          FROM all_tab_cols -- Just use a big table
+      FROM all_tab_cols -- Just use a big table
-      )
+  )
-      SELECT proc_date
+  SELECT proc_date
-        FROM date_list
+    FROM date_list
-       WHERE proc_date BETWEEN '02-FEB-17'
+   WHERE proc_date BETWEEN '02-FEB-17'
-                           AND '26-MAR-18'
+                       AND '26-MAR-18'
-    ORDER BY proc_date
+ORDER BY proc_date
-    ;
+;
- </code>
+</code>