Home » SQL & PL/SQL » SQL & PL/SQL » date field error
date field error [message #249517] Wed, 04 July 2007 13:32 Go to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hello you all,

I have two tables and I need to update an indicator of table_a for every address in table_a, which exists in table_b having an age less than 18.

The code beneath is just a code snipped to give you an indication, but the essence is that when i include (sysdate - (to_date(b.BIRTHDATE , 'yyyymmdd')))/365 is doesn't work.

Can anyone help me out why this doesn't work or what I should do to make it work?


update table_a
set indicator = 'J'
WHERE (SELECT 1
from table_a a , table_b b
       where a.ADRESNUMBER  = b.ADRESNUMBER
       and   a.POSTALCODE   = b.POSTALCODE
       and   a.STREET       = b.STREET
       and (sysdate - (to_date(b.BIRTHDATE , 'yyyymmdd')))/365 <= 18);



thanks in advance



Re: date field error [message #249520 is a reply to message #249517] Wed, 04 July 2007 13:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ "doesn't work" is not a valid Oracle error
2/ if BIRTHDATE is a date TO_DATE(BIRTHDATE) is an error
3/ the substraction of 2 dates is in unit of days not year or what else.

Regards
Michel
Re: date field error [message #249521 is a reply to message #249520] Wed, 04 July 2007 13:56 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hello Michel,

Thanks for your reply, but BIRTDATE is a varchar and not a date field. Actually, the BIRHDATE in table_b is a DATE field, but I don't use that. I just use the BIRTHDAY field in table_a which is a varchar.

Re: date field error [message #249522 is a reply to message #249521] Wed, 04 July 2007 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So see 3/

Regards
Michel
Re: date field error [message #249523 is a reply to message #249522] Wed, 04 July 2007 14:06 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
I know Michel,

that's why I divide it with 365, to get the years!..

regards
Re: date field error [message #249525 is a reply to message #249523] Wed, 04 July 2007 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is the error?
What is the meaning of "is doesn't work"?
How can we know that?

Have a look at MONTHS_BETWEEN function.

Regards
Michel

[Updated on: Wed, 04 July 2007 14:17]

Report message to a moderator

Re: date field error [message #249583 is a reply to message #249525] Thu, 05 July 2007 02:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
A where clause should be resolved to a boolean.
How can a (select 1) be considered true or false?
Re: date field error [message #249584 is a reply to message #249583] Thu, 05 July 2007 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Doh! I even didn't see it.

The error message should be welcome but he seems to be reluctant to give it. Mad

Regards
Michel
Re: date field error [message #249654 is a reply to message #249521] Thu, 05 July 2007 08:04 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Jolanda wrote on Wed, 04 July 2007 14:56

Actually, the BIRHDATE in table_b is a DATE field, but I don't use that.



Quote:
and (sysdate - (to_date(b.BIRTHDATE , 'yyyymmdd')))/365 <= 18);




./fa/1587/0/
Re: date field error [message #249685 is a reply to message #249517] Thu, 05 July 2007 09:55 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Not a good idea, you are not handling leap year correctly. use


and add_months(to_date(b.BIRTHDATE , 'yyyymmdd'))),18 * 12) <= trunc(sysdate)




Re: date field error [message #249693 is a reply to message #249685] Thu, 05 July 2007 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said I prefer MONTHS_BETWEEN. Wink

Regards
Michel
Re: date field error [message #249695 is a reply to message #249517] Thu, 05 July 2007 10:05 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
The months_between will work fine. That is the beauty of Oracle.... 20 different ways to do the exact same action.
Re: date field error [message #249697 is a reply to message #249693] Thu, 05 July 2007 10:17 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
hello you all,

thank you all for your replies. they all do work fine, because I have tested them all.

I have done some testing and the issue is that the data is corrupt and some date fields are not filled or filled with characters which are not identified as date fields by Oracle (as it should be Smile ). So, the to_date function returns errors.

so a better question could be:

you need to select and update records for a specific condition (like setting an indicator for everyone under 18), but you don't have control over de data you get. So, the date field, which is delivered as a Varchar can contain anything. Is there a way to tell SQL to ignore fields you don't recognize as dates and continu with the select and update statement?

thanks you all

Regards
Re: date field error [message #249703 is a reply to message #249517] Thu, 05 July 2007 10:42 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Why do you have the birth date as a date column in one table (correct) and as a varchar2 in the other table (garbage). As for continuing, what do you want to do if the date is bad?
Previous Topic: Search Result Page query
Next Topic: Performance issue post parallelism change
Goto Forum:
  


Current Time: Sun Dec 04 04:36:42 CST 2016

Total time taken to generate the page: 0.07550 seconds