sqloraclesql-date-functionssqldatetime

Creating date table from dual

I have been trying to figure out how to create a table out of this code, but can not figure it out. Thank You so much for any answers.

WITH 
  dat_param AS (SELECT SYSDATE AS dat FROM dual)

SELECT 
  LEVEL AS day_year
  ,TRUNC((SELECT dat FROM dat_param),'YYYY')+LEVEL-1 AS date_year
  ,TO_CHAR(TRUNC((SELECT dat FROM dat_param),'YYYY')+LEVEL-1,'YYYY-MM') AS part_year
  ,TO_NUMBER(TO_CHAR(TRUNC((SELECT dat FROM dat_param),'YYYY')+LEVEL-1,'D')) AS day_week

FROM   dual
CONNECT BY LEVEL <= ADD_MONTHS(TRUNC((SELECT dat FROM dat_param),'YEAR'),12)-TRUNC((SELECT dat FROM dat_param),'YEAR')
Solution

  • Assuming you are using Oracle, you can use CREATE TABLE AS:

    CREATE TABLE t as
    WITH 
      dat_param AS (SELECT SYSDATE AS dat FROM dual)    
    SELECT 
      LEVEL AS day_year
      ,TRUNC((SELECT dat FROM dat_param),'YYYY')+LEVEL-1 AS date_year
      ,TO_CHAR(TRUNC((SELECT dat FROM dat_param),'YYYY')+LEVEL-1,'YYYY-MM') AS part_year
      ,TO_NUMBER(TO_CHAR(TRUNC((SELECT dat FROM dat_param),'YYYY')+LEVEL-1,'D')) AS day_week    
    FROM   dual
    CONNECT BY LEVEL <= ADD_MONTHS(TRUNC((SELECT dat FROM dat_param),'YEAR'),12)-TRUNC((SELECT dat FROM dat_param),'YEAR');

    Here is a db<>fiddle.