?
Solved

Date and calendar in oracle

Posted on 2014-10-23
12
Medium Priority
?
427 Views
Last Modified: 2014-10-29
http://www.experts-exchange.com/Database/Oracle/Q_28316594.html

The above is closed.

CREATE TABLE TAB_DATA
(
  DAY_OF_MONTH             VARCHAR2(10 BYTE),
  AU                       VARCHAR2(30 BYTE),
  TP                       VARCHAR2(30 BYTE),
  BOTH                     VARCHAR2(30 BYTE),
  TOT                      VARCHAR2(30 BYTE),
  LCM                      VARCHAR2(30 BYTE),
  CURRENT_MONTH_FIRST_DAY  DATE,
  DAY_OF_WEEK              VARCHAR2(30 BYTE)
)


Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('1', '1/110', '0/0', '0/0', '1/110', 
    '8/392', TO_DATE('06/01/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Monday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('2', '5/156', '1/31', '0/0', '6/187', 
    '12/588', TO_DATE('06/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Tuesday  ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('3', '6/181', '0/0', '0/0', '6/181', 
    '12/588', TO_DATE('06/03/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Wednesday');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('4', '1/39', '1/23', '2/55', '4/117', 
    '11/539', TO_DATE('06/04/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Thursday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('5', '1/38', '0/0', '1/280', '2/318', 
    '8/392', TO_DATE('06/05/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Friday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('6', '5/175', '0/0', '2/89', '7/264', 
    '9/392', TO_DATE('06/06/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Saturday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('7', '2/52', '1/16', '0/0', '3/68', 
    '5/245', TO_DATE('06/07/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Sunday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('8', '3/63', '0/0', '0/0', '3/63', 
    '8/392', TO_DATE('06/08/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Monday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('9', '2/73', '2/51', '1/20', '5/144', 
    '12/588', TO_DATE('06/09/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Tuesday  ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('10', '3/116', '0/0', '1/20', '4/136', 
    '12/588', TO_DATE('06/10/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Wednesday');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('11', '3/70', '0/0', '0/0', '3/70', 
    '11/539', TO_DATE('06/11/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Thursday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('12', '1/27', '0/0', '0/0', '1/27', 
    '8/392', TO_DATE('06/12/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Friday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('13', '3/179', '2/47', '0/0', '5/226', 
    '9/441', TO_DATE('06/13/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Saturday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('14', '0/0', '0/0', '2/50', '2/50', 
    '5/245', TO_DATE('06/14/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Sunday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('15', '1/25', '0/0', '0/0', '1/25', 
    '8/392', TO_DATE('06/15/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Monday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('16', '1/29', '0/0', '2/55', '3/84', 
    '12/588', TO_DATE('06/16/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Tuesday  ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('20', '1/29', '1/29', '1/31', '3/89', 
    '9/441', TO_DATE('06/20/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Saturday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('18', '3/90', '2/118', '0/0', '5/208', 
    '11/539', TO_DATE('06/18/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Thursday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('19', '0/0', '2/46', '1/31', '3/77', 
    '8/392', TO_DATE('06/19/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Friday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('21', '2/61', '1/77', '0/0', '3/138', 
    '5/245', TO_DATE('06/21/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Sunday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('22', '3/140', '1/27', '0/0', '4/167', 
    '8/392', TO_DATE('06/22/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Monday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('23', '2/74', '1/20', '1/20', '4/114', 
    '12/588', TO_DATE('06/23/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Tuesday  ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('24', '2/43', '0/0', '0/0', '2/43', 
    '12/588', TO_DATE('06/24/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Wednesday');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('25', '2/56', '0/0', '0/0', '2/56', 
    '11/539', TO_DATE('06/25/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Thursday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('29', '0/0', '1/39', '1/42', '2/81', 
    '8/392', TO_DATE('06/29/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Monday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('27', '2/120', '0/0', '0/0', '2/120', 
    '9/441', TO_DATE('06/27/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Saturday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('28', '0/0', '2/46', '0/0', '2/46', 
    '5/245', TO_DATE('06/28/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Sunday   ');
COMMIT;

Open in new window


We find some missing dates and hence my calendar is missing days and dates.

Is there a way to still get DAY_OF_MONTH, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK with other values being zeros?

Help is appreciated.
0
Comment
Question by:anumoses
  • 6
  • 5
12 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40400025
what do you expect the output to be?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40400044
Do you just need NVL on your results?

nvl(au,0) au,
nvl(tp,0) tp,
nvl(both,0) both,
nvl(tot,0) tot,
nvl(lcm,0) lcm,
0
 
LVL 6

Author Comment

by:anumoses
ID: 40400050
For 17th  June 2015 I need as follows

17   0/0  0/0  0/0  0/0  0/0  6/17/2015   Wednesday

and also for any missing dates
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 74

Accepted Solution

by:
sdstuber earned 1500 total points
ID: 40400121
WITH s1 AS (SELECT TO_DATE('01-mar-2014', 'dd-mon-yyyy') startdate FROM DUAL),
     t
     AS (    SELECT NEXT_DAY(startdate - 7, 'Sun') + (LEVEL - 1) h_date
               FROM s1
         CONNECT BY LEVEL <= 999)
  SELECT TO_CHAR(t.h_date, 'dd') day_of_month,
         NVL(au, '0/0') au,
         NVL(tp, '0/0') tp,
         NVL(both, '0/0') both,
         NVL(tot, '0/0') tot,
         NVL(lcm, '0/0') lcm,
         TO_CHAR(t.h_date, 'mm/dd/yyyy') h_date,
         TO_CHAR(t.h_date, 'fmDay') day_of_week,
         current_month_first_day
    FROM tab_data a, t
   WHERE a.current_month_first_day(+) = t.h_date
ORDER BY t.h_date


I left the current_month_first_day on, based on original query, your output doesn't show it, but it's null, so I couldn't tell if it was missing or simply not visible
0
 
LVL 6

Author Comment

by:anumoses
ID: 40400146
I was looking at 17th june 2015 data and it was missing data (6/17/2015) current_month_first_day
0
 
LVL 21

Assisted Solution

by:flow01
flow01 earned 500 total points
ID: 40400163
change the nvl result value,  show  t.h_date instead of a.current_month_first_day and use another format for day of week

with s1
as
(select to_date('01/03/2014','dd/mm/yyyy') startdate,  '0/0' null_value from dual)
,
t
as
(
select case to_char(startdate,'d')
       when '1' then startdate
       else
        trunc(startdate,'day')
       end
       + (level - 1) h_date
      from s1
      connect by level <= 999
)
SELECT to_char(t.h_date,'dd') day_of_month, to_char(t.h_date,'Dy') day_of_week ,
   nvl(au,s1.null_value) au ,
   nvl(tp,s1.null_value) tp,
   nvl(both,s1.null_value) both,
   nvl(tot,s1.null_value) tot,
   nvl(lcm,s1.null_value) lcm,
   t.h_date h_date,
   to_char(t.h_date,'Day') day_of_week2
   from tab_data a, t, s1
   where a.current_month_first_day(+) = t.h_date
   and t.h_date < add_months(startdate,3)
order by t.h_date
/
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40400205
>>> I was looking at 17th june 2015 data and it was missing data (6/17/2015) current_month_first_day


that's what I mentioned above.  Your expected results didn't include it.
but -  it's simply the generated h_date (whether populated or not, due to the join)

and, based on your expected results I was displaying that as the 2nd to last column already.

but, it's easy enough to return it twice


WITH s1 AS (SELECT TO_DATE('01-mar-2014', 'dd/mon/yyyy') startdate FROM DUAL),
     t
     AS (    SELECT NEXT_DAY(startdate - 7, 'Sun') + (LEVEL - 1) h_date
               FROM s1
         CONNECT BY LEVEL <= 999)
  SELECT TO_CHAR(t.h_date, 'dd') day_of_month,
         NVL(au, '0/0') au,
         NVL(tp, '0/0') tp,
         NVL(both, '0/0') both,
         NVL(tot, '0/0') tot,
         NVL(lcm, '0/0') lcm,
         TO_CHAR(t.h_date, 'mm/dd/yyyy') h_date,
         TO_CHAR(t.h_date, 'fmDay') day_of_week,
         h_date current_month_first_day
    FROM tab_data a, t
   WHERE a.current_month_first_day(+) = t.h_date
ORDER BY t.h_date


and, if you want to make sure it's formatted as a string dd/mm/yyyy then use to_char on it like I did on the h_date column above it
0
 
LVL 6

Author Comment

by:anumoses
ID: 40400270
with s1
as
(select to_date('01/06/2015','dd/mm/yyyy') startdate,  '0/0' null_value from dual)
,
t
as
(
select case to_char(startdate,'d')
       when '1' then startdate
       else
        trunc(startdate,'day')
       end
       + (level - 1) h_date
      from s1
      connect by level <= 999
)
SELECT to_char(t.h_date,'dd') day_of_month, to_char(t.h_date,'Dy') day_of_week ,
   nvl(au,s1.null_value) au ,
   nvl(tp,s1.null_value) tp,
   nvl(both,s1.null_value) both,
   nvl(tot,s1.null_value) tot,
   nvl(lcm,s1.null_value) lcm,
   t.h_date h_date,
   to_char(t.h_date,'Day') day_of_week2
   from tab_data a, t, s1
   where a.current_month_first_day(+) = t.h_date
   and t.h_date < add_months(startdate,3)
order by t.h_date


Is there a way to limit to the month? (June 2015)
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1500 total points
ID: 40400299
yes, you can limit to a single month, just adjust the "t" query


WITH s1 AS (SELECT TO_DATE('01-mar-2014', 'dd/mon/yyyy') startdate FROM DUAL),
     t
     AS (SELECT *
           FROM (    SELECT NEXT_DAY(startdate - 7, 'Sun') + (LEVEL - 1) h_date
                       FROM s1
                 CONNECT BY NEXT_DAY(startdate - 7, 'Sun') + (LEVEL - 1) <= DATE '2015-06-30')
          WHERE h_date >= DATE '2015-06-01')
  SELECT TO_CHAR(t.h_date, 'dd') day_of_month,
         NVL(au, '0/0') au,
         NVL(tp, '0/0') tp,
         NVL(both, '0/0') both,
         NVL(tot, '0/0') tot,
         NVL(lcm, '0/0') lcm,
         TO_CHAR(t.h_date, 'mm/dd/yyyy') h_date,
         TO_CHAR(t.h_date, 'fmDay') day_of_week,
         h_date current_month_first_day
    FROM tab_data a, t
   WHERE a.current_month_first_day(+) = t.h_date
ORDER BY t.h_date
0
 
LVL 6

Author Comment

by:anumoses
ID: 40409161
WHERE h_date >= DATE '2015-06-01')

Can the date be a parameter where the user enters a date?

parameter is date format.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1500 total points
ID: 40409186
yes

use this if your parameter is a DATE type
WHERE h_date >= :your_parameter


use this if your parameter is text, change the format to whatever matches your text
WHERE h_date >= to_date(:your_parameter,'yyyy-mm-dd hh24:mi:ss')


note, there are 2 dates in that query

CONNECT BY NEXT_DAY(startdate - 7, 'Sun') + (LEVEL - 1) <= DATE '2015-06-30')

you'll probably want to make a parameter in the same type for the upper bound condition too
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40410615
Thanks for the help
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question