Lastday of the month ORA-01846: not a valid day of the week

building semantic layer
1

Hi,

I am trying to create a object in the universe to check the last day of the month on the existing date object.

LastDayObj-1

Date - 1
Where
TO_CHAR(TO_DATE(LAST_DAY(TRUNC (DATE)),‘DAY’)) = ‘SATURDAY’

LastDayObj-2

Date - 2
Where
TO_CHAR(TO_DATE(LAST_DAY(TRUNC (DATE)),‘DAY’)) = ‘SUNDAY’

It’s giving me the ORA-01846: not a valid day of the week :-1846

Any help.
Thanks in advance…

suntra (BOB member since 2003-02-06)

2

Okay, so use last_day(date) to get the date, or use to_char(last_day(date),‘DAY’) to get the day name.

What are you trying to do here? First of all, you don’t need to to_date() on the last_day(), the last_day() function already returns a date value. And you don’t need to trunc(date), as the last_day will take care of that too. I don’t understand this at all.

Do you want the last day of the month? or the last “Saturday” of the month? Those are two different things…

Here is some code that will return the date for the last Saturday of the month, using Oracle functions:

last_day(sysdate) - (6 - to_char(last_day(sysdate), 'D'))

Running this code today I get Sep 27, 2003. That would be the last Saturday for the month of September.

If that’s not what you are looking for, try posting a more specific example with some sample data. 8)

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Hi Dave,

Thanks for the reply. But I am looking for a different solution to get the last working day of the month ignoring if the last day of the month is Saturday/Sunday.

What I was trying to achieve here is, I need to get the value of last working day of the previous month.

for ex: If the run date of the current report is 07/31/03
I need to get the Value of 06/30/03 ( As 06/30th is Monday…it works fine and I am happy with the report result)

 Lets say, for example if last day of previous month (i.e. 30th of previous month) is SATURDAY/SUNDAY then I need to get the show the values of FRIDAY i.e. 28th.

       I need to get the value of 
          if 30th Sunday ---> then 28th (i.e. Last_day(Date) - 2)
             30th Satday ---> then 28th  (i.e. Last_day(Date) - 1)

Hope you got what I was trying here.

Appreciate your help…
Thanks in advance.

suntra (BOB member since 2003-02-06)

4

suntra,

Instead of creating two objects use a CASE WHEN END to check the LAST DAY of Month and appropriately do -1 or -2 .

Sri

Sridharan :india: (BOB member since 2002-11-08)

5

Try this


SELECT
   CASE
   	WHEN (TO_CHAR(LAST_DAY(SYSDATE),'DAY')) LIKE 'SUN%'
	THEN
        	LAST_DAY(SYSDATE) - 2
	WHEN (TO_CHAR(LAST_DAY(SYSDATE),'DAY')) LIKE 'SAT%'
	THEN
        	LAST_DAY(SYSDATE) - 1
	ELSE
        	LAST_DAY(SYSDATE)
    END 
FROM
    DUAL

Sri

Sridharan :india: (BOB member since 2002-11-08)

6 

TO_CHAR(TO_DATE(LAST_DAY(TRUNC (DATE)),'DAY')) 
It's giving me the ORA-01846: not a valid day of the week :-1846

Standard rule is that you cannot format any Date column using TO_DATE. If you wish to, then you need to convert Date into TO_CHAR first and then to TO_DATE. That is the reason for the error

Rephrase of your condition :
TO_CHAR(LAST_DAY(DATE),‘DAY’) . This would suffice your check.

Satish Kurella (BOB member since 2003-08-18)

7

Thanks guys. Your inputs were really helpful.

suntra (BOB member since 2003-02-06)