Quick Oracle Question

suarve · 22 Sep 2007 at 18:00

Hi all,

I have a date formatted in 'dd/mm/yyyy' in a table. How exactly do i format this so i can print the date in the following format in a query:
"Tuesday December 20, 2007"

I have tried a few things including the below with no luck:

"to_char(MYDATEFIELD,'Day, Month DD, YYYY')"

Any help appeciated.

Ta

suarve · 23 Sep 2007 at 12:49

Anyone?

Haircut · 23 Sep 2007 at 18:35

What you have posted should work.

If I do:

Code:

SELECT TO_CHAR(datefield, 'Day, Month DD, YYYY') from table;

I get the following result:
Wednesday, December 17, 1980

EDIT: I should point out that your date is not actually stored in the format you posted in the table.
What you see when querying it is dependent on the NLS_DATE_FORMAT setting in Oracle.

suarve · 24 Sep 2007 at 16:26

Thanks for the reply.

How could i find the last Friday in a month (given i have a date, say '02-SEPT-95').

Thanks

Haircut · 24 Sep 2007 at 19:20

You should be able to do this with a combination of the LAST_DAY and NEXT_DAY functions.

LAST_DAY(date) returns the last day in the given month.
NEXT_DAY(date,day) returns the next day specified in the text, i.e. NEXT_DAY('24-Sep-2007','FRI') will return the next Friday after today.

Code:

SELECT TO_CHAR
             (
               NEXT_DAY(LAST_DAY(TO_DATE('02-SEP-1995','MM')) - 7,
               TO_CHAR(TO_DATE('02-SEP-1995','DD-MM-YYYY'),'FRI')
                        ),'DD.MM.YYYY'
             )
FROM dual;

Should do it.

suarve · 25 Sep 2007 at 14:36

Haircut said:
You should be able to do this with a combination of the LAST_DAY and NEXT_DAY functions.

LAST_DAY(date) returns the last day in the given month.
NEXT_DAY(date,day) returns the next day specified in the text, i.e. NEXT_DAY('24-Sep-2007','FRI') will return the next Friday after today.

Code:

SELECT TO_CHAR ( NEXT_DAY(LAST_DAY(TO_DATE('02-SEP-1995','MM')) - 7, TO_CHAR(TO_DATE('02-SEP-1995','DD-MM-YYYY'),'FRI') ),'DD.MM.YYYY' ) FROM dual;

Should do it.

excellent, thank you!

suarve · 26 Sep 2007 at 17:06

Hi again,

I'm just going through a few examples using the EMP and DEPT tables included with oracle. Basically, i want to find employees who earn more than 'JONES' and display it all follows:

Code:

 ENAME    SAL     JOB            ENAME   SAL       JOB
_________________________________________________________
SCOTT    3000   ANALYST     JONES     2975     MANAGER
KING       5000   PRESIDENT   JONES     2975     MANAGER
FORD      3000   ANALYST     JONES     2975    MANAGER

I have used a sub-query to gain the people who earn more than 'JONES':

SELECT ENAME, SAL, JOB
FROM EMP
WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = ‘JONES’);

which gives:

Code:

 ENAME    SAL     JOB            
________________________
SCOTT    3000   ANALYST    
KING       5000   PRESIDENT   
FORD      3000   ANALYST

but cannot figure out how to display the last 3 columns. Can anyone point me in the right direction?

Before you ask, this isn't homework, im just going through a few exercises for myself.

Thanks