CODEWITHSUNDEEP
sqloracle-databasesql-date-functionssqldatetime
Jakub Vlček
Jakub Vlček

Reputation: 35

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')

Upvotes: 1

Views: 447

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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.

Upvotes: 1

Related Questions