Select Between Dates Format

Hi,

SELECT ename, job, TO_CHAR(MONTHS_BETWEEN('O1-JAN-81','31-DEC-81'), 'MM/DD/YY') HIREDATE, CONCAT(CONCAT(ename, job),' ,') EMPLOYEE
FROM emp e
WHERE ename >= ALL
      (SELECT ename
       FROM emp e, dept d
       WHERE e.deptno = d.deptno
      );
      
I want SELECT, months between date1 & date2 as column heading.

How do I do that?

Thanks.

(SQLPLUS)

LVL 1
suredazzleAsked:
Who is Participating?
 
suredazzleConnect With a Mentor Author Commented:

Never mind guys, I already got what I am looking for.
0
 
Mark GeerlingsDatabase AdministratorCommented:
In Oracle you can get whatever heading you want by supplying the value for the heading in double quotes following the column in your query.  For example:

select ename "Name", hiredate "Date Hired"
from emp;

This will return the results in two columns something like this (the date format will depend on your setting for NLS_DATE_FORMAT):

Name        Date Hired
--------      -------------
John Doe  01/10/2008
Jane Doe  03/15/2008


Your question was not clear to me, because you are selecting five columns, but it looks like you want only two column headings ("SELECT" and  "months between date1 & date2").  Is that what you are asking for?
0
 
suredazzleAuthor Commented:
Hi Markgeer,

So, you can rename column like  "Name"  without AS too.

It should be 4 columns. MONTHS_BETWEEN(date1, date2).

I want it like string as stated above.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
suredazzleAuthor Commented:
I don't you use AS in AND conditional.

Only in Select.


	 AND e.hiredate BETWEEN TO_DATE('O1-JAN-81','MM/DD/YY') AND TO_DATE('31-DEC-81','MM/DD/YY') AS "HIREDATE"

Open in new window

0
 
suredazzleAuthor Commented:
Revised the code, it said not valid month.

Still working.....
SELECT ename, job, hiredate "date hired", CONCAT(CONCAT(ename, job),' ,') EMPLOYEE
FROM emp e
WHERE ename >= ALL
	(SELECT ename
	 FROM emp e, dept d
	 WHERE e.deptno = d.deptno
	)
AND hiredate BETWEEN TO_DATE('O1-JAN-81','MM/DD/YY') AND TO_DATE('31-DEC-81','MM/DD/YY');

Open in new window

0
 
rpkhareCommented:
Try using this:

DateDiff(Month,Date1,Date2')
0
 
suredazzleAuthor Commented:
I need to find who started between 1/01/81 thru 12/31/81.

I think it's GETDATE().
0
 
rpkhareCommented:
Use:

HireDate Between Date1 and Date2.
0
 
suredazzleAuthor Commented:

Please take a look at revised code, per ID # below.

11.08.2008 at 07:04PM PST, ID: 22914910

Keep say '31-DEC-81' not a valid month. Not reading the string...
Might need CONVERT function.
0
 
suredazzleAuthor Commented:


I mean the code from the beginning.
0
 
suredazzleAuthor Commented:

Markgeer, I saw 1 of your posting on this...NVLS_DATE_FORMAT...


Let me see what I can do.
0
 
suredazzleAuthor Commented:

Thanks Mark!

 It turns out not quite enough...
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Why are you using  TO_DATE('O1-JAN-81','MM/DD/YY') , not TO_DATE('O1-JAN-81','DD-MMM-YY') ? The date formats in string and format mask have to comply.
0
 
suredazzleAuthor Commented:
Qlemo, it supposed to run TO_CHAR format.

BETWEEN  TO_DATE('01-JAN-81', 'DD-MON-YY') AND TO_DATE('31-DEC-81','DD-MM-YY')

Listed all dates, not between specific dates.




AND hiredate BETWEEN TO_CHAR(TO_DATE('01-JAN-81'), 'MM/DD/YY') AND TO_CHAR(TO_DATE('31-DEC-81'),'MM/DD/YY')

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Sorry, of course I was writing about to_char:

between TO_CHAR('01-JAN-81', 'DD-MON-YY') and TO_CHAR('31-DEC-81', 'DD-MON-YY')
0
 
suredazzleAuthor Commented:

Thanks!

Still working.....maybe MONTHS_BETWEEN.

Let's see.
0
 
suredazzleAuthor Commented:
It returns 1, suppose to be more than that...
BETWEEN TO_CHAR(TO_DATE('01-JAN-81'), 'DD-MON-YY') AND TO_CHAR(TO_DATE('31-DEC-81'),'DD-MON-YY')

Open in new window

0
 
suredazzleAuthor Commented:

Okay, Convert not working.

What am I missing?


 
AND hiredate BETWEEN CONVERT('01-JAN-81','01/01/81') AND CONVERT('31-DEC-81','12/31/81')

Open in new window

0
 
SharathData EngineerCommented:
check this,
AND hiredate BETWEEN CAST('01-JAN-81' as datetime) AND Cast('31-DEC-81' as datetime)
0
 
suredazzleAuthor Commented:

Sharath_123,
It return 1 person as what I got before.
It should be more....Thanks!
0
 
suredazzleAuthor Commented:

Put in outer or inner, still got 1.
0
 
SharathData EngineerCommented:
can you provide sample data in emp and dept tables?
0
 
suredazzleAuthor Commented:
SQL> DESC EMP;
Name                                 Null?    Type
HIREDATE                                  DATE
DEPTNO                            NOT NULL NUMBER(2)

SQL> DESC DEPT;
 Name                                 Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                            NOT NULL NUMBER(2)


0
 
suredazzleAuthor Commented:
Sharath_123,

EMPNO ENAME      JOB             MGR HIREDATE          SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7876 ADAMS      CLERK            7788 12-JAN-83         1100
      20

Hope it helps!
0
 
suredazzleAuthor Commented:

Sharath_123,

Did you get 1 return or more?
0
 
suredazzleAuthor Commented:
Hi Guys,

Revised the inner query.

Yeap, it won't take function. Return all dates as before....

Remove it the function. hmmm...let's see!

WHERE hiredate >= TO_DATE('01-JAN-81', 'DD-MON-YY')
AND hiredate < TO_DATE('31-DEC-81', 'DD-MON-YY')
AND e.deptno = d.deptno

Open in new window

0
 
suredazzleAuthor Commented:

Stick with before as orig function. Getting 2....

Not giving up!
0
 
Mark GeerlingsDatabase AdministratorCommented:
It looks like you are asking two questions:
1. How to control (or rename) the column headings
2. How to get the rows you want

Is that correct?
0
 
awking00Commented:
Can you post precisely what you want your output to be, including the column heading(s) that you want?
0
 
SharathData EngineerCommented:
If you got the  solution, post it and close the question.
0
 
suredazzleAuthor Commented:
SELECT DISTINCT(NAME), JOB, HIRED_DATE, EMPLOYEE
FROM
     (SELECT e.ename "NAME", e.job "JOB", TO_CHAR(TO_DATE(e.hiredate),'DD/MM/YY') "HIRED_DATE",
      CONCAT(CONCAT(e.ename, e.job), ' ,') AS "EMPLOYEE"
      FROM emp e, dept d
      WHERE e.hiredate BETWEEN '01-JAN-81' AND '31-DEC-81'
      AND d.deptno = e.deptno
      );
=====================================

NAME         JOB           HIRED_DA EMPLOYEE
---------- --------- -------- ---------------------
ALLEN         SALESMAN  20/02/81 ALLEN      SALESMAN  ,
BLAKE         MANAGER   01/05/81 BLAKE      MANAGER   ,
CLARK         MANAGER   09/06/81 CLARK      MANAGER   ,
FORD         ANALYST   03/12/81 FORD      ANALYST   ,
JAMES         CLERK     03/12/81 JAMES      CLERK        ,
JONES         MANAGER   02/04/81 JONES      MANAGER   ,
KING         PRESIDENT 17/11/81 KING      PRESIDENT ,
MARTIN         SALESMAN  28/09/81 MARTIN      SALESMAN  ,
TURNER         SALESMAN  08/09/81 TURNER      SALESMAN  ,
WARD         SALESMAN  22/02/81 WARD      SALESMAN  ,

10 rows selected.

0
 
Mark GeerlingsDatabase AdministratorCommented:
Be very careful with the "select distinct..." syntax in Oracle!  The results you get may surprise you, especially in cases like this when you select multiple columns and one of them is a "date" column.  Also, this forces a "sort" operation on the result set, so depending on how many records this involves, that could add a significant performance penalty.
0
 
suredazzleAuthor Commented:

It's conversion of date!

NLS_DATE_FORMAT is an alternative for alter session.

Best to stick with script as now. :)
0
 
suredazzleAuthor Commented:

Markgeer,

Per our discussion, NLV has proper setting. Quick minor changed, that's it.

Instead "20/2/81", it's 2/20/81.

 HIRED_DA
----------
02/20/81
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.