Home » SQL & PL/SQL » SQL & PL/SQL » confusions about the query
confusions about the query [message #353677] Tue, 14 October 2008 10:37 Go to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi,

I have a table where i have declared a column of date, as varchar2(30) data type, now how do i query to get to know the non date values ?

when i issue to_date(), the query is not showing any errors.

can any one pls help me out on this..


regards
suhas
Re: confusions about the query [message #353678 is a reply to message #353677] Tue, 14 October 2008 10:49 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

That means you have all the data in that column which is of valid date format...
Re: confusions about the query [message #353682 is a reply to message #353677] Tue, 14 October 2008 11:05 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@SUHAS22845,

The to_date function returns an error, if you try to convert a string, that is not a valid date.
You can use this behavior to construct a function to run a check for your column.

Tom has a good example here

Hope this helps.

Regards,
Jo
Re: confusions about the query [message #353697 is a reply to message #353677] Tue, 14 October 2008 12:45 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
SUHAS22845 wrote on Tue, 14 October 2008 11:37

I have a table where i have declared a column of date, as varchar2(30) data type



Why would any reasonable person do this?
Some people inherit a poorly designed database and they have to deal with it, cursing the creator, but by your own admission, you are the one who created it yourself. I guess the person to take this over will know who to curse at.
Re: confusions about the query [message #353734 is a reply to message #353677] Tue, 14 October 2008 22:58 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Making date field 'VARCHAR2' is poor design anyway . The chances are more for the application to store non-combatible date strings in this field.

Thumbs Up
Rajuvan.

[Updated on: Tue, 14 October 2008 22:59]

Report message to a moderator

Previous Topic: Passing a table name as parameter
Next Topic: extracting strings
Goto Forum:
  


Current Time: Thu Dec 08 14:18:01 CST 2016

Total time taken to generate the page: 0.25586 seconds