creating_date_ranges

Differences

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

Link to this comparison view

Next revision
Previous revision
Last revisionBoth sides next revision
creating_date_ranges [2018/05/13 16:58] – created davidcreating_date_ranges [2018/05/13 17:03] david
Line 1: Line 1:
 +====== SQL Server ======
 <code sql> <code sql>
 WITH date_range AS ( WITH date_range AS (
Line 22: Line 23:
                    AND DateAdd(DAY,  DateDiff(DAY,   0, GetDate()), 0)  -- Today                    AND DateAdd(DAY,  DateDiff(DAY,   0, GetDate()), 0)  -- Today
 ORDER BY TheDate DESC 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 -- 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 -- 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 -- 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) -- 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>
  • creating_date_ranges.txt
  • Last modified: 2021/01/12 21:08
  • by 127.0.0.1