Building a list of dates [message #394864] |
Mon, 30 March 2009 11:10  |
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 #394871 is a reply to message #394868] |
Mon, 30 March 2009 12:02   |
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   |
Frank
Messages: 7901 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 #395199 is a reply to message #394864] |
Tue, 31 March 2009 10:58   |
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   |
joy_division
Messages: 4963 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 #395208 is a reply to message #395207] |
Tue, 31 March 2009 12:32   |
Frank
Messages: 7901 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   |
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   |
joy_division
Messages: 4963 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   |
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   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
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   |
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   |
joy_division
Messages: 4963 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   |
Frank
Messages: 7901 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   |
joy_division
Messages: 4963 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.
|
|
|
|
|