OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL (Oracle 11g)

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

SQL [message #619986]

Sun, 27 July 2014 07:20

ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai

Senior Member

select c1, c2
from table t1,t2
where t1.id = t2.id and 
      to_char(t1.dtm_val,'dd-mon-yyyy H24:MM:SS') between to_char('02-jun-2014 14:12:03','dd-mon-yyyy H24:mm:ss') and
      to_char('02-jun-2014 14:12:03','dd-mon-yyyy H24:mm:ss')

Getting ORA-01722 invalid number

I think i have given right value?

Report message to a moderator

Re: SQL [message #619987 is a reply to message #619986]

Sun, 27 July 2014 07:28

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

This
Quote:

to_char('02-jun-2014 14:12:03','dd-mon-yyyy H24:mm:ss')

is attempting to convert string to a string.

Report message to a moderator

Re: SQL [message #619989 is a reply to message #619986]

Sun, 27 July 2014 07:32

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

ORAGENASHOK wrote on Sun, 27 July 2014 17:50

and 
      to_char(t1.dtm_val,'dd-mon-yyyy H24:MM:SS') between to_char('02-jun-2014 14:12:03','dd-mon-yyyy H24:mm:ss') and
      to_char('02-jun-2014 14:12:03','dd-mon-yyyy H24:mm:ss')

Assuming, t1.dtm_val is a DATE data type.

and t1.dtm_val between to_date('02-jun-2014 14:12:03','dd-mon-yyyy H24:mi:ss') 
and to_date('02-jun-2014 14:12:03','dd-mon-yyyy H24:mi:ss')

Another thing, the time part having minutes should be "mi" and not "mm", it is months in 2 digits.

Regadrs,
Lalit

Edit : Modified "mm" to "mi" with reason explained.

[Updated on: Sun, 27 July 2014 07:36]

Report message to a moderator

Re: SQL [message #619992 is a reply to message #619989]

Sun, 27 July 2014 07:47

ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai

Senior Member

Its giving " ORA - 01821: date format not recognized

Report message to a moderator

Re: SQL [message #619995 is a reply to message #619992]

Sun, 27 July 2014 07:56

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I'm sure you can debug Lalit's minor typing error. Look up the data picture formats.

Report message to a moderator

Re: SQL [message #619996 is a reply to message #619992]

Sun, 27 July 2014 07:57

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is "Its"?
COPY AND PASTE what you do and get.

In addition: between '02-jun-2014 14:12:03' and '02-jun-2014 14:12:03' there is not so much time. Razz

Report message to a moderator

Re: SQL [message #619997 is a reply to message #619995]

Sun, 27 July 2014 08:05

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

John Watson wrote on Sun, 27 July 2014 18:26

I'm sure you can debug Lalit's minor typing error. Look up the data picture formats.

Actually I just copy pasted OP's predicate, so didn't notice the missing "h". Even Michel noticed something more logical than the technical things we pointed. There are so many things wrong with the query though.

Report message to a moderator

Re: SQL [message #619998 is a reply to message #619996]

Sun, 27 July 2014 08:08

ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai

Senior Member

yes dont have much difference Razz

i can see that he changed mm to mi and to_char to to_date

but i have tried with both

when i change to_date to to_char

am getting

ORA-01722 invalid number

[Updated on: Sun, 27 July 2014 08:08]

Report message to a moderator

Re: SQL [message #619999 is a reply to message #619998]

Sun, 27 July 2014 08:12

ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai

Senior Member

yes now i got that 'h', thanks Smile

Report message to a moderator

Re: SQL [message #620000 is a reply to message #619998]

Sun, 27 July 2014 08:17

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Sun, 27 July 2014 14:57

What is "Its"?
COPY AND PASTE what you do and get.
...

Report message to a moderator

Re: SQL [message #620002 is a reply to message #619999]

Sun, 27 July 2014 08:26

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

ORAGENASHOK wrote on Sun, 27 July 2014 18:42

yes now i got that 'h', thanks

So please post what you tried finally. Will help others looking for similar issue.

Report message to a moderator

Re: SQL [message #620004 is a reply to message #620002]

Sun, 27 July 2014 08:53

ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai

Senior Member

and t1.dtm_val between to_date('02-jun-2014 14:12:03','dd-mon-yyyy H24:mi:ss') 
and to_date('02-jun-2014 14:12:03','dd-mon-yyyy H24:mi:ss'

Modifed

and t1.dtm_val between to_date('02-jun-2014 14:12:03','dd-mon-yyyy HH24:mi:ss') 
and to_date('02-jun-2014 14:12:03','dd-mon-yyyy HH24:mi:ss'

Thanks Lalit Smile

Report message to a moderator

Re: SQL [message #620005 is a reply to message #620004]

Sun, 27 July 2014 09:39

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Hope you also understood what Michel pointed out. Your between condition is nothing but "=" condition logically unless you have an interval.

Just a suggestion, from next time please show your session details using copy paste from SQL*Plus. It will have lot of error details for a quick turnaround.

And thanks for the feedback.

[Updated on: Sun, 27 July 2014 10:08]

Report message to a moderator

Previous Topic:	Calculating Index Growth
Next Topic:	find different or missing rows in one table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 19:08:53 CDT 2024