How to populate calendar table in Oracle?
This is a simple and easy way to do it
with calendar as (
select :startdate + rownum - 1 as day
from dual
connect by rownum < :enddate - :startdate
)
select rownum as "S.No", to_date(day,'dd_mm_yyyy') as "Cal_Dt", to_char(day,'day') as "DayName"
from calendar
How to create Calendar Table in Oracle
connect by level clause may be used :
select FromDate+level-1 "Date", Description "HolidayName"
from holiday
connect by level <= ToDate - FromDate + 1
group by FromDate+level-1, Description
order by 1;
demo
How to populate a calendar in Oracle DB and join it to a list of Names from another table
Try this:
select staff_name, dt from staff_list
cross join (select (TRUNC(SYSDATE,'YEAR')-1) + level as dt from dual connect by level <= ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12)-TRUNC(SYSDATE,'YEAR'))
Calendar table in SQL
You can use a Oracle row generator:
insert into your_table ( your_column )
select
to_date('2000/01/01', 'yyyy/mm/dd') + N.n
from
(SELECT ROWNUM n
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <=
SYSDATE
- to_date('2000/01/01', 'yyyy/mm/dd')
+ 1
) T
) N
where
to_date('2000/01/01', 'yyyy/mm/dd') + N.n <= SYSDATE
How to insert a Calender Items into Oracle DB records?
In Oracle, you can use a recursive query to generate the date series, and then generate the expected columns in the outer query:
create table dim_time_table as
select
dt full_date,
extract(day from dt) day,
to_char(dt, 'month') month_name,
extract(month from dt) month_number,
extract(year from dt) year
from (
select to_date('1995-01-01', 'yyyy-mm-dd') + level - 1 as dt
from dual
connect by
to_date('1995-01-01', 'yyyy-mm-dd') + level
<= to_date('1997-01-01', 'yyyy-mm-dd')
)
Demo on DB Fiddle:
FULL_DATE | DAY | MONTH_NAME | MONTH_NUMBER | YEAR
:-------- | --: | :--------- | -----------: | ---:
01-JAN-95 | 1 | january | 1 | 1995
02-JAN-95 | 2 | january | 1 | 1995
03-JAN-95 | 3 | january | 1 | 1995
04-JAN-95 | 4 | january | 1 | 1995
05-JAN-95 | 5 | january | 1 | 1995
06-JAN-95 | 6 | january | 1 | 1995
07-JAN-95 | 7 | january | 1 | 1995
...
Oracle SQL: How can I create a date table or date view using a date field in a table
Use virtual columns:
CREATE TABLE calendar (
dt DATE PRIMARY KEY,
day NUMBER(2,0) GENERATED ALWAYS AS (EXTRACT(DAY FROM dt)),
month NUMBER(2,0) GENERATED ALWAYS AS (EXTRACT(MONTH FROM dt)),
month_name VARCHAR2(9)
GENERATED ALWAYS AS (
CAST(
TO_CHAR(dt, 'fmMonth', 'NLS_DATE_LANGUAGE=ENGLISH')
AS VARCHAR2(9)
)
),
year NUMBER(4,0) GENERATED ALWAYS AS (EXTRACT(YEAR FROM dt))
);
Then:
INSERT INTO calendar (dt)
SELECT DATE '2020-01-01' FROM DUAL UNION ALL
SELECT DATE '2020-01-03' FROM DUAL;
The output of:
SELECT * FROM calendar;
Is:
DT DAY MONTH MONTH_NAME YEAR 2020-01-01 00:00:00 1 1 January 2020 2020-01-03 00:00:00 3 1 January 2020 Generate a range of dates using SQL
There's no need to use extra large tables or ALL_OBJECTS table:
SELECT TRUNC (SYSDATE - ROWNUM) dt
FROM DUAL CONNECT BY ROWNUM < 366will do the trick.
How to use Oracle SQL to create a calendar
You can do this much more simply by doing:
WITH dts AS (SELECT TRUNC(to_date('&year', 'yyyy'), 'yyyy') + LEVEL -1 AS dt
FROM dual
CONNECT BY LEVEL <= to_char(TO_DATE(&YEAR || '1231', 'yyyymmdd'), 'ddd')),
dts2 AS (SELECT dt,
TRUNC(dt, 'mm') dt_mon,
TRUNC(dt, 'iw') dt_start_of_week,
to_char(dt, 'fmdd') day_of_month
FROM dts)
SELECT to_char(dt_mon, 'fmMonth') "MONTH",
row_number() OVER (PARTITION BY to_char(dt_mon, 'fmMonth') ORDER BY dt_start_of_week) week_num,
MAX(CASE WHEN dt = dt_start_of_week THEN day_of_month END) mon,
MAX(CASE WHEN dt = dt_start_of_week + 1 THEN day_of_month END) tue,
MAX(CASE WHEN dt = dt_start_of_week + 2 THEN day_of_month END) wed,
MAX(CASE WHEN dt = dt_start_of_week + 3 THEN day_of_month END) thu,
MAX(CASE WHEN dt = dt_start_of_week + 4 THEN day_of_month END) fri,
MAX(CASE WHEN dt = dt_start_of_week + 5 THEN day_of_month END) sat,
MAX(CASE WHEN dt = dt_start_of_week + 6 THEN day_of_month END) sun
FROM dts2
GROUP BY dt_mon,
to_char(dt_mon, 'fmMonth'),
dt_start_of_week
ORDER BY dt_mon, dt_start_of_week;If you want the week to start with a Sunday, you can change the dt_start_of_week column to be
trunc(dt + 1, 'iw') - 1 dt_start_of_weekin the dts2 subquery, as well as changing the aliases of the columns from mon-sun to sun-sat.This works by finding the iso week start day, which is always a Monday. Then you can use that, to group by, plus throw a row_number analytic function over it to find the week of the month number of that row.
Generate One year dates using Oracle SQL
The concept of "month" is variable, so you may want to double check if "6 months" is really what you're looking for. It can give unexpected results if you don't know the oddities of date math.
That being said, a calendar which contains 6 months on either side of the current date can be generated with the following. Note that 184 days is the longest a 6 month span can last (July-December).
SELECT dt
FROM (SELECT TRUNC(SYSDATE - 184) + LEVEL AS dt
FROM dual
CONNECT BY LEVEL <= 369)
WHERE dt BETWEEN add_months(TRUNC(SYSDATE), -6) AND add_months(TRUNC(SYSDATE), 6);
Related Topics
Why Does Isnumeric('.') Return 1
How to Select Multiple Rows Filled with Constants
How to Pass a List as a Parameter in a Stored Procedure
Sql, How to Concatenate Results
Calculate the Number of Records for Each Date Between 2 Dates
Do All Columns in a Select List Have to Appear in a Group by Clause
Mysql, Reshape Data from Long/Tall to Wide
Postgres - Function to Return the Intersection of 2 Arrays
What Is Causing Foreign Key Mismatch Error
SQL Insert into Database with Apostrophe
How to Select Even Records from a Table in Oracle
How to Select Date Without Time in SQL
Closing Connection When Using Dapper
Get Month from Datetime in SQLite
Autoincrement in Oracle to Already Created Table
Use '=' or Like to Compare Strings in SQL