Last date and first day of the month.

[haneen97]

Hi,

Can I use Last_day(table_date) and first day? I have the query below and it is not working. If not is there any suggestions? I error is pointing at the "First_day".

SQL> SELECT to_char(to_date(first_day(s.key_date),'YYYYMMDD'),'MM/DD/YYYY') START_DATE,
2 to_char(to_date(last_day(s.key_date),'YYYYMMDD'),'MM/DD/YYYY') END_DATE
3 FROM schedule s;
SELECT to_char(to_date(first_day(s.key_date),'YYYYMMDD'),'MM/DD/YYYY') START_DATE,
*
ERROR at line 1:
ORA-00904: invalid column name


Thanks.

Mo

 

[carp]

The first date of any month is always "1" and will be given by TRUNC(your_date,'Month'). There is no first_day function, which would explain why Oracle doesn't like your code.

Elbert, CO
0909 MST

 

[MKVAB]

FIRST_DAY is not a valid Oracle function. You will need to use LAST_DAY to get the FIRST_DAY.

LAST_DAY([date_field])+1

Hope that helps.

-Mary Klaire

 

[MKVAB]

Oops, sorry carp didn't see yours when I replied. Anyway, error in my LAST_DAY, meant to use ADD_MONTHS-1 to get to the previous month first. Oops, shouldn't have been in such a hurry.

 

[haneen97]

Did you mean like this? please not the error message!
SQL> SELECT to_char(to_date(LAST_DAY(ADD_MONTHS(s.key_date,1)),'YYYYMMDD'),'MM/DD/YYYY') START_DATE
,
2 to_char(to_date(last_day(s.key_date),'YYYYMMDD'),'MM/DD/YYYY') END_DATE
3 FROM schedule s;
SELECT to_char(to_date(LAST_DAY(ADD_MONTHS(s.key_date,1)),'YYYYMMDD'),'MM/DD/YYYY') START_DATE,
*
ERROR at line 1:
ORA-01861: literal does not match format string

Thanks a lot.

Mo

 

[carp]

Since your inner functions are returning a date, you don't need the TO_DATE. Try instead

SELECT to_char(LAST_DAY(ADD_MONTHS(s.key_date,1)),'MM/DD/YYYY') START_DATE

 

[haneen97]

Still not there yet please see the error below

SQL> SELECT to_char(LAST_DAY(ADD_MONTHS(s.key_date,1)),'MM/DD/YYYY') START_DATE,
2 to_char(to_date(last_day(s.key_date),'YYYYMMDD'),'MM/DD/YYYY') END_DATE
3 FROM schedule s;
SELECT to_char(LAST_DAY(ADD_MONTHS(s.key_date,1)),'MM/DD/YYYY') START_DATE,
*
ERROR at line 1:
ORA-01861: literal does not match format string



Mo

 

[carp]

I think the problem is your data:

09:42:21 SQL> SELECT to_char(LAST_DAY(ADD_MONTHS(sysdate,1)),'MM/DD/YYYY') START_DATE FROM dual;

START_DATE
----------
01/31/2004

The syntax works; therefore I think s.key_date is not really a date. Perhaps it is a character string that needs to be mapped? In that case, try

SELECT
to_char(LAST_DAY(
ADD_MONTHS(
TO_DATE(s.key_date,'YYYY/MM/DD')
,1)),'MM/DD/YYYY') START_DATE FROM dual;

Elbert, CO
0950 MST

 

[haneen97]

Thanks carp, that gave me the correct data, but I get a group by error.

to_char(LAST_DAY(ADD_MONTHS(TO_DATE(s.key_date,'YYYY/MM/DD'),1)),'MM/DD/YYYY') START_DATE,
*
ERROR at line 5:
ORA-00979: not a GROUP BY expression

How do I reference this field in the group by exprestion?

Thanks a lot.

Mo

 

[carp]

GROUP BY s.key_date