Hi there,
I have a table named Performance, which includes a date field named PDATE, some other attributes such as title, etc, and a field named WEEKDAY, which includes the numerical value for the day of the week. Here is an example:
| PER# | P# | THEATRE# | WEEKDAY | PDATE | PHOUR | PMINUTE | COMMENTS |
|---|
| 1 | 1 | 1 | 1 | 02-MAR-10 | 19 | 30 | Normal evening time |
| 2 | 1 | 1 | 2 | 03-MAR-10 | 19 | 30 | Normal evening time |
| 58 | 6 | 6 | 3 | 04-MAR-10 | 19 | 30 | Normal evening time |
| 5 | 1 | 1 | 4 | 05-MAR-10 | 19 | 30 | Normal evening time |
I went to verify the day of the week in a calendar (in fact I checked more than one) and I noticed that the Weekday value seems incorrect. For instance, the 2nd of March was a Tuesday so I would expect WEEKDAY to say 3 (considering Sunday to be the 1st day of the week, which is the universal norm), or perhaps 2 (considering Monday to be the 1st day of the week).
First thing that came to mind is, since the WEEKDAY is a number field which has been pre-populated, the person who populated it made a mistake.
So I went to verify this by running a query that would give me the correct day of the week by extracting it from the PDATE field:
SELECT DISTINCT pdate, EXTRACT(year from pdate) year,
to_char(to_date(pdate,'DD/MM/YYYY'),'DY') weekdaycalc, weekday
FROM ops$yyang00.Performance
ORDER BY pdate
And to my astonishment, I got the following table as a return:
| PDATE | YEAR | WEEKDAYCALC | WEEKDAY |
|---|
| 02-MAR-10 | 2010 | SUN | 1 |
| 03-MAR-10 | 2010 | MON | 2 |
| 04-MAR-10 | 2010 | TUE | 3 |
| 05-MAR-10 | 2010 | WED | 4 |
| 06-MAR-10 | 2010 | THU | 5 |
| 07-MAR-10 | 2010 | FRI | 6 |
| 08-MAR-10 | 2010 | SAT | 7 |
| 09-MAR-10 | 2010 | SUN | 1 |
| 10-MAR-10 | 2010 | MON | 2 |
As you can see, all of the above WEEKDAYCALC values are incorrect, and seem to match the incorrect values in WEEKDAY.
I could perhaps understand some weird WEEKDAY numbering if there is some general database setting where I could specify any day of the week to be the first day. However, I can't think of any logical explanation on why Oracle would call a Tuesday Sunday.
Could someone please advise what I mght be doing wrong? This is driving me insane!
Thank you in advance for all the help and support.
Regards,
P.