Home » SQL & PL/SQL » SQL & PL/SQL » query between dates (was: anyone can resolve this query)
query between dates (was: anyone can resolve this query) [message #254756] Fri, 27 July 2007 22:24 Go to next message
sathyam2627
Messages: 52
Registered: November 2006
Member
Hi to all,

I am trying to fetch the employees who has joined in between 01/may/2007 and 30/jun/2007. I am getting output like this.

select emp_name,
to_char(emp_join_dt,'fmDD/MM/YYYY') "Joined Dt"
from emp_mst
where to_char(emp_join_dt,'DD/MM/YYYY') between '01/05/2007' and '30/06/2007'

/

EMP_NAME Joined Dt
------------------------------ ----------
K ANANTHAMURTHY 20/8/1980
V SHANKAR 18/7/1980
S ARUN KUMAR 23/6/1980
R HEMARAJU 8/3/1980
P SUNDARESHAN 19/11/1979
S NAGENDRA 6/9/1979
S RAM1IAH 1/7/1979
P SHANMUGAM 21/8/1978
N SUDHEENDRA 6/5/1978
R SRINIVASAIAH 10/3/1978

Anybody can resolve this query..

Thanks & Regards
sathya

[Updated on: Sat, 28 July 2007 00:49] by Moderator

Report message to a moderator

Re: anyone can resolve this query [message #254760 is a reply to message #254756] Sat, 28 July 2007 00:35 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that you got the correct answer. Oracle returned exactly what you've asked for. But, your question was wrong. You used CHARACTERS instead of DATES.

Therefore, something like this might return a different result set:
SELECT emp_name, TO_CHAR (emp_join_dt, 'fmDD/MM/YYYY') "Joined Dt"
  FROM emp_mst
 WHERE emp_join_dt BETWEEN TO_DATE ('01/05/2007', 'dd/mm/yyyy')
                       AND TO_DATE ('30/06/2007', 'dd/mm/yyyy');
Re: anyone can resolve this query [message #254782 is a reply to message #254760] Sat, 28 July 2007 03:28 Go to previous messageGo to next message
sathyam2627
Messages: 52
Registered: November 2006
Member
Thanking You
i got correct answer what i looking for. could you please tell me little bit briefly, whats wrong with my query

select emp_name,
to_char(emp_join_dt,'fmDD/MM/YYYY') "Joined Dt"
from emp_mst
where to_char(emp_join_dt,'DD/MM/YYYY') between '01/05/2007' and '30/06/2007'
/
in the above query i am asking for the exact match of the characters in between 01/05/2007' and '30/06/2007'

and corrected query is
SELECT emp_name, TO_CHAR(emp_join_dt, 'fmDD/MM/YYYY')"Joined dt"
FROM kec_emp_mst
WHERE emp_join_dt BETWEEN TO_DATE ('01/05/2007', 'dd/mm/yyyy')
AND TO_DATE ('30/06/2007', 'dd/mm/yyyy')
/

could you please clarify to me.

kindly help me regarding this.

Once again thanking you

sathya
Re: anyone can resolve this query [message #254784 is a reply to message #254782] Sat, 28 July 2007 03:57 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'01/05/2007' and '30/06/2007' are strings and not date, this is the error.
When you compare strings '01/12/9999' is lower than '30/06/2007' as '0' is lower than '3'.
And '18/7/1980' is greater than '01/05/2007' as '1' is greater than '0'.

Regards
Michel
Previous Topic: is "cursor for update " cause locking
Next Topic: Update the date upon record update
Goto Forum:
  


Current Time: Sat Dec 10 03:26:31 CST 2016

Total time taken to generate the page: 0.05735 seconds