Home » SQL & PL/SQL » SQL & PL/SQL » Date ,NVL Query (Oracle 10g,Win XP)
Date ,NVL Query [message #410266] Thu, 25 June 2009 22:54 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Whats wrong in this .Please help

Select D.* from scott.emp D
where deptno=20 AND ( ( TO_CHAR(HIREDATE,'DD/MM/YYYY') BETWEEN TO_CHAR(&DAT,'DD/MM/YYYY')
AND TO_CHAR(&DAT2,'DD/MM/YYYY') )
OR NVL(TO_CHAR(&DAT,'DD/MM/YYYY'),TO_CHAR(SYSDATE,'DD/MM/YYYY') )=TO_CHAR(SYSDATE,'DD/MM/YYYY') )
Re: Date ,NVL Query [message #410267 is a reply to message #410266] Thu, 25 June 2009 22:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
rakeshramm wrote on Thu, 25 June 2009 20:54
Whats wrong in this .Please help

Select D.* from scott.emp D
where deptno=20 AND ( ( TO_CHAR(HIREDATE,'DD/MM/YYYY') BETWEEN TO_CHAR(&DAT,'DD/MM/YYYY')
AND TO_CHAR(&DAT2,'DD/MM/YYYY') )
OR NVL(TO_CHAR(&DAT,'DD/MM/YYYY'),TO_CHAR(SYSDATE,'DD/MM/YYYY') )=TO_CHAR(SYSDATE,'DD/MM/YYYY') )




We give up.
What is wrong?
I don't see anything wrong?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Date ,NVL Query [message #410268 is a reply to message #410266] Thu, 25 June 2009 22:58 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
May I know why are you using to_char everywhere in the code?

By
Vamsi
Re: Date ,NVL Query [message #410270 is a reply to message #410266] Thu, 25 June 2009 23:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
1) there is nothing wrong with it. You have not provided an error message, you have not provided an explanation of bad data content that says results are not as expected. so, there is nothing wrong with it.

2) there is clearly something wrong with it. You are doing TO_CHAR on dates. Why?

3) your substituion variables do not have quotes around them. Why not?

Before you reply back "thanks Kev, after putting quotes in it works", tell us why you are using to_char.

Also, exaplain the problem. Did you really expect someone to figure out a solution to a problem you never stated? Geesh.

Kevin
Re: Date ,NVL Query [message #410273 is a reply to message #410267] Thu, 25 June 2009 23:15 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Select D.* from scott.emp D
where deptno=20 AND ( ( TO_CHAR(HIREDATE,'DD/MM/YYYY') BETWEEN TO_CHAR(&DAT,'DD/MM/YYYY')
AND TO_CHAR(&DAT2,'DD/MM/YYYY') )
OR NVL(TO_CHAR(&DAT,'DD/MM/YYYY'),TO_CHAR(SYSDATE,'DD/MM/YYYY') )=TO_CHAR(SYSDATE,'DD/MM/YYYY') )


&DAT =25/6/2009
&dat2=25/6/2009

ORA-01481: invalid number format mode

Re: Date ,NVL Query [message #410275 is a reply to message #410273] Thu, 25 June 2009 23:18 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Do you mind reading all posts?
Quote:
3) your substituion variables do not have quotes around them. Why not?
Before your next post, read all the messages and answers all the questions.

By
Vamsi
Re: Date ,NVL Query [message #410276 is a reply to message #410273] Thu, 25 June 2009 23:19 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Select D.* 
 from scott.emp D
 where deptno=20 
 AND   (NVL(:DAT, sysdate) = sysdate
  OR   TO_CHAR(HIREDATE,'DD/MM/YYYY') BETWEEN TO_CHAR(:DAT,'DD/MM/YYYY') AND TO_CHAR(:DAT2,'DD/MM/YYYY')  )


Thanks Al of then i got it correct ,Again Thanks
Re: Date ,NVL Query [message #410282 is a reply to message #410276] Fri, 26 June 2009 00:04 Go to previous messageGo to next message
nitinkumarbe
Messages: 8
Registered: June 2009
Junior Member

thanks for the post, It helped me to correect my problem. I was facing the same issue with NVL method and for me its working fine now

Thanks again
Re: Date ,NVL Query [message #410288 is a reply to message #410276] Fri, 26 June 2009 00:21 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Still you are using to_char....
God bless you!

You will realize soon.

By
Vamsi
Re: Date ,NVL Query [message #410290 is a reply to message #410282] Fri, 26 June 2009 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Before you reply back "thanks Kev, after putting quotes in it works", tell us why you are using to_char.

Still this error in your code.
May I make you notice that '02/02/2000' is between '01/01/2009' and '31/12/2010':
SQL> select 'OK' from dual 
  2  where '02/02/2000' between '01/01/2009' and '31/12/2010';
'O
--
OK

1 row selected.

Regards
Michel
Re: Date ,NVL Query [message #410389 is a reply to message #410290] Fri, 26 June 2009 14:40 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
ORA-12541: TNS: no listener
Whatever we shout, he is not going to listen.
In his perspective, he got the answer. That's it. Mad

By
Vamsi
Re: Date ,NVL Query [message #410395 is a reply to message #410389] Fri, 26 June 2009 15:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think you post this in the appropriate topic.

Regards
Michel
Re: Date ,NVL Query [message #410396 is a reply to message #410395] Fri, 26 June 2009 15:10 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I meant for this topic only.....
As he is not listening to us, I posted "ORA-12541: TNS: no listener" Razz

By
Vamsi
Re: Date ,NVL Query [message #410398 is a reply to message #410395] Fri, 26 June 2009 15:11 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Actually Michel, I think Vamsi made quite a clever response. If I interpret it correctly, he is using an Oracle error just to make a point that the OP is not going to listen to what we say because the solution worked in the one case for him.
Re: Date ,NVL Query [message #410399 is a reply to message #410396] Fri, 26 June 2009 15:19 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Embarassed Was too subtle for me... Confused

Regards
Michel
Previous Topic: insufficient privs error
Next Topic: Timestamp in sybase! In oracle?
Goto Forum:
  


Current Time: Sat Dec 10 08:50:28 CST 2016

Total time taken to generate the page: 0.07809 seconds