Home » SQL & PL/SQL » SQL & PL/SQL » Building a list of dates
Building a list of dates [message #394864] Mon, 30 March 2009 11:10 Go to next message
bskiff
Messages: 27
Registered: May 2005
Junior Member
I have table, workdate, with the column wdate (date). It basically has an entry for work days, minus weekends and holidays. I'm trying to write a query that will display all days in a month, with and indicator whether it's a workday or not. Something like this:

MAR-1-09
MAR-2-09 X
MAR-3-09 X
MAR-4-09 X
MAR-5-09 X
MAR-6-09 X
MAR-7-09
etc.

I know I need to somehow join the workdate table to dual, but I'm not getting it. I also need to pass in month and year parameters. Can someone point me in the right direction with this?

Thanks!
Re: Building a list of dates [message #394868 is a reply to message #394864] Mon, 30 March 2009 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

if you SEARCH this forum for "calendar" you will find answers to this FAQ!
Re: Building a list of dates [message #394871 is a reply to message #394868] Mon, 30 March 2009 12:02 Go to previous messageGo to next message
bskiff
Messages: 27
Registered: May 2005
Junior Member
Sorry, I did search and there were hundreds of hits, but I couldn't find an example of what I'm trying to do though. It does seem that calendars are a sensitive topic here though.
Re: Building a list of dates [message #394877 is a reply to message #394871] Mon, 30 March 2009 12:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ok, what you need is a row generator. You need some dummy table (physical or just in memory) which rows can represent all the days in the month. Outer joining that to your table, will reveal for each day if it is a workday or not.
There are several ways to generate such a dummy table without physically creating one.

One way is to use an existing table with at least 31 rows. eg
all_objects.

Another way (depending on your version) is to apply a little trick as seen here
Re: Building a list of dates [message #394879 is a reply to message #394877] Mon, 30 March 2009 12:32 Go to previous messageGo to next message
bskiff
Messages: 27
Registered: May 2005
Junior Member
Thanks, that helps a lot!
Re: Building a list of dates [message #395199 is a reply to message #394864] Tue, 31 March 2009 10:58 Go to previous messageGo to next message
bskiff
Messages: 27
Registered: May 2005
Junior Member
This is what I came up with. Seems to work pretty well.

select calendar.WorkDate, decode(wd.ndate, null, 'F', 'T') worked
from
(
select to_date(DayNum.Dt || '/' || :month || '/' || :year, 'dd/mm/yyyy') WorkDate
from
(select rownum Dt
from ALL_OBJECTS
where rownum <= (select to_char(last_day('01-' || :month || '-' || :year),'dd') from dual)) DayNum
) calendar
LEFT OUTER JOIN ref_workdate wd ON calendar.WorkDate = wd.ndate
Re: Building a list of dates [message #395206 is a reply to message #395199] Tue, 31 March 2009 12:27 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
bskiff wrote on Tue, 31 March 2009 11:58

where rownum <= (select to_char(last_day('01-' || :month || '-' || :year),'dd') from dual)) DayNum



This doesn't make any sense, unless I am misreading it.
Re: Building a list of dates [message #395207 is a reply to message #394864] Tue, 31 March 2009 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Re: Building a list of dates [message #395208 is a reply to message #395207] Tue, 31 March 2009 12:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You need to help US by stopping to spam the forum with these ridiculous and totally unnecessary posts.
The guy already told us what he wanted, he came back to show us what he achieved. (I don't see a question or a request for help)
Re: Building a list of dates [message #395209 is a reply to message #395206] Tue, 31 March 2009 12:53 Go to previous messageGo to next message
bskiff
Messages: 27
Registered: May 2005
Junior Member
joy_division wrote on Tue, 31 March 2009 13:27
bskiff wrote on Tue, 31 March 2009 11:58

where rownum <= (select to_char(last_day('01-' || :month || '-' || :year),'dd') from dual)) DayNum



This doesn't make any sense, unless I am misreading it.


I did that to determine the number of days in a month/year combination.
Re: Building a list of dates [message #395232 is a reply to message #395209] Tue, 31 March 2009 15:36 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
bskiff wrote on Tue, 31 March 2009 13:53
joy_division wrote on Tue, 31 March 2009 13:27
bskiff wrote on Tue, 31 March 2009 11:58

where rownum <= (select to_char(last_day('01-' || :month || '-' || :year),'dd') from dual)) DayNum



This doesn't make any sense, unless I am misreading it.


I did that to determine the number of days in a month/year combination.


The two problems with it are:

1. ROWNUM is a number and you are comparing it to a string.
2. LAST_DAY operates on a DATE and you are using it on a STRING.
FOO SCOTT>select last_day('01-' || 'MAY' || '-' || '2009') from dual;
select last_day('01-' || 'MAY' || '-' || '2009') from dual
                                       *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

[Updated on: Tue, 31 March 2009 15:38]

Report message to a moderator

Re: Building a list of dates [message #395235 is a reply to message #395232] Tue, 31 March 2009 16:01 Go to previous messageGo to next message
bskiff
Messages: 27
Registered: May 2005
Junior Member
joy_division wrote on Tue, 31 March 2009 16:36
bskiff wrote on Tue, 31 March 2009 13:53
joy_division wrote on Tue, 31 March 2009 13:27
bskiff wrote on Tue, 31 March 2009 11:58

where rownum <= (select to_char(last_day('01-' || :month || '-' || :year),'dd') from dual)) DayNum



This doesn't make any sense, unless I am misreading it.


I did that to determine the number of days in a month/year combination.


The two problems with it are:

1. ROWNUM is a number and you are comparing it to a string.
2. LAST_DAY operates on a DATE and you are using it on a STRING.
FOO SCOTT>select last_day('01-' || 'MAY' || '-' || '2009') from dual;
select last_day('01-' || 'MAY' || '-' || '2009') from dual
                                       *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected





It does work. There may be a better way to do this, but I would not have posted it if it didn't work.

SQL> select to_char(last_day('01-' || 'feb' || '-' || '2008'),'dd') from dual;

TO
--
29

SQL>
Re: Building a list of dates [message #395261 is a reply to message #395235] Tue, 31 March 2009 23:42 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Is this what you are looking for ..

SQL> SELECT dt || CASE
          WHEN to_char (dt, 'D') NOT IN (6, 7)
             THEN ' -- X '
       END dt
  FROM (SELECT     trunc (sysdate, 'MM') + LEVEL - 1 AS dt
              FROM dual
        CONNECT BY LEVEL < = 10)

DT             
---------------
01-APR-09 -- X 
02-APR-09 -- X 
03-APR-09 -- X 
04-APR-09      
05-APR-09      
06-APR-09 -- X 
07-APR-09 -- X 
08-APR-09 -- X 
09-APR-09 -- X 
10-APR-09 -- X 

10 rows selected.


Thanks
Trivendra
Re: Building a list of dates [message #395432 is a reply to message #395261] Wed, 01 April 2009 07:50 Go to previous messageGo to next message
bskiff
Messages: 27
Registered: May 2005
Junior Member
Thanks Trivendra. I need to have the month and year as variables. My colleague suggested I use the following query. It's similar to what you suggested. Keep in mind that for this example, I hard coded the variables.

select to_date(rownum || '/' || 'Mar' || '/' || 2009, 'dd/mm/yyyy') WorkDate
from DUAL
CONNECT BY LEVEL <= (to_char(last_day('01-' || 'Mar' || '/' || 2009),'dd'))
Re: Building a list of dates [message #395438 is a reply to message #395235] Wed, 01 April 2009 07:58 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
bskiff wrote on Tue, 31 March 2009 17:01
joy_division wrote on Tue, 31 March 2009 16:36
bskiff wrote on Tue, 31 March 2009 13:53
joy_division wrote on Tue, 31 March 2009 13:27
bskiff wrote on Tue, 31 March 2009 11:58

where rownum <= (select to_char(last_day('01-' || :month || '-' || :year),'dd') from dual)) DayNum



This doesn't make any sense, unless I am misreading it.


I did that to determine the number of days in a month/year combination.


The two problems with it are:

1. ROWNUM is a number and you are comparing it to a string.
2. LAST_DAY operates on a DATE and you are using it on a STRING.
FOO SCOTT>select last_day('01-' || 'MAY' || '-' || '2009') from dual;
select last_day('01-' || 'MAY' || '-' || '2009') from dual
                                       *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected





It does work. There may be a better way to do this, but I would not have posted it if it didn't work.

SQL> select to_char(last_day('01-' || 'feb' || '-' || '2008'),'dd') from dual;

TO
--
29

SQL>


You probably learned in shcool a saying that goes something like this:

Just because it works in every example you show doesn't prove it is valid. If there is just one case where it doesn't work, then it proves it is invalid.

So, in this case, in your exact environment for you, it may work, but you are asking for trouble. One day, someone will change the default date format of the database or Oracle will with an evil grin remove implicit conversion and all of your code will fail.

It's best to code correctly rather than take the approach that why should you make the extra effort to do it if it works in your case.

Just a piece of friendly advice.
Re: Building a list of dates [message #395443 is a reply to message #395432] Wed, 01 April 2009 08:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
bskiff wrote on Wed, 01 April 2009 14:50
Thanks Trivendra. I need to have the month and year as variables. My colleague suggested I use the following query. It's similar to what you suggested. Keep in mind that for this example, I hard coded the variables.

select to_date(rownum || '/' || 'Mar' || '/' || 2009, 'dd/mm/yyyy') WorkDate
from DUAL
CONNECT BY LEVEL <= (to_char(last_day('01-' || 'Mar' || '/' || 2009),'dd'))


to_date('01/MAR/2009', 'dd/mm/yyyy')
These formats don't comply
Re: Building a list of dates [message #395446 is a reply to message #395443] Wed, 01 April 2009 08:12 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Yeah Frank it doesn't, but believe it or not, Oracle somehow may implicitly convert it correctly in some cases.

FOO SCOTT>alter session set nls_date_format='mm/dd/yyyy';

Session altered.

FOO SCOTT>select to_date('01/MAR/2009', 'dd/mm/yyyy') from dual;

TO_DATE('0
----------
03/01/2009


Incontheivable!

Just another reason to use DATEs properly.
Re: Building a list of dates [message #395452 is a reply to message #394864] Wed, 01 April 2009 08:37 Go to previous messageGo to next message
bskiff
Messages: 27
Registered: May 2005
Junior Member
I agree with you guys, my solution may not be the best. I didn't know how to do this, that's why I came here asking for advice. I didn't get a heck of a lot...so this this what I came up with. The question still stands. If someone can suggest a better way, I'm very interested to hear it. I always try to do things the best way, and I'm certainly not too proud to admit my idea my not always be the best.

That said, what do you guys think is the safest way to build this calendar (I can join it to the other table, so don't consider that part), and have it be able to accept month and year parameters?

Thanks!

PS - Didn't mean for this question to cause so much drama Wink
Re: Building a list of dates [message #395453 is a reply to message #395452] Wed, 01 April 2009 08:44 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Some day back I saw an example

http://www.orafaq.com/node/2029

may be this can help you

Thanks
Trivendra
Previous Topic: Typical Function
Next Topic: execution problem
Goto Forum:
  


Current Time: Thu Dec 08 04:11:56 CST 2016

Total time taken to generate the page: 0.28554 seconds