[mkasem]

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 a lot

[andrewst]

There is an Oracle function called LAST_DAY that returns last day of month for given date, but not FIRST_DAY. For first day of month use TRUNC(s.key_date,'MM')

[mkasem]

What about formatting it like this ('MM/DD/YYYY'), remeber the actual table date is in 8 bytes varchar2.

Thanks

[andrewst]

My first reponse to that is: "why?" Why would anyone store dates as VARCHAR2 and not as DATE? Does not compute...

Anyway, you must convert the VARCHAR2 to a DATE before applying any other functions that work with dates:

LAST_DAY( TO_DATE(s.key_date,'MM/DD/YYYY') )

TRUNC( TO_DATE(s.key_date,'MM/DD/YYYY'), 'MM' )