OraFAQ Forum: SQL & PL/SQL » query between dates (was: anyone can resolve this query)

Home » SQL & PL/SQL » SQL & PL/SQL » query between dates (was: anyone can resolve this query)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query between dates (was: anyone can resolve this query) [message #254756]

Fri, 27 July 2007 22:24

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

Littlefoot
Messages: 21823
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');

Report message to a moderator

Re: anyone can resolve this query [message #254782 is a reply to message #254760]

Sat, 28 July 2007 03:28

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

Report message to a moderator

Re: anyone can resolve this query [message #254784 is a reply to message #254782]

Sat, 28 July 2007 03:57

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 19:06:43 CST 2024