Home » SQL & PL/SQL » SQL & PL/SQL » Convert 1/1/1900 to NULL (oracle10204)
Convert 1/1/1900 to NULL [message #423841] Mon, 28 September 2009 10:06 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
HI,

there is a field in the table which is defined as date. DUe to some incorrect load processing the values for some of the fields is 1/1/1900. Most fields are correctly populated with dates.

Instead of cirrecting huge amount of data, for now we want to display all 1/1/1900 as NULL else show whatever data is in the date field.

For 1/1/1900 i want tp show as follows, null for all other i want to show the same value

date_field dervived_date_field

1/1/1900
2/2/2009 2/2/2009



The following SQL is displaying 01-JAN-00; I am not able to get it to display null. ANy ideas?

select date_filed, decode(date_filed,'01-Jan-1900' ,null,date_filed)

thanks
Re: Convert 1/1/1900 to NULL [message #423842 is a reply to message #423841] Mon, 28 September 2009 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NULLIF

Regards
Michel
Re: Convert 1/1/1900 to NULL [message #423848 is a reply to message #423841] Mon, 28 September 2009 10:43 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Use to_date on that date of yours.. My thought is that it might be comparing the string '01-JAN-00' (implicitly converted to a string) to '01-JAN-1900' (the string you gave in).

This gives me 28-SEP-09:
select decode(sysdate, '28-SEP-2009', null, sysdate) from dual


This gives me null:
select decode(sysdate, '28-SEP-09', null, sysdate) from dual


This gives me null
select decode(trunc(sysdate), to_date('28-SEP-2009', 'DD-MON-YYYY'), null, sysdate) from dual
Re: Convert 1/1/1900 to NULL [message #423849 is a reply to message #423848] Mon, 28 September 2009 10:50 Go to previous messageGo to next message
bella13
Messages: 90
Registered: July 2005
Member
This is great! thanks for your help.
Re: Convert 1/1/1900 to NULL [message #423852 is a reply to message #423848] Mon, 28 September 2009 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select decode(sysdate, '28-SEP-2009', null, sysdate) from dual
  2  /
DECODE(SYSDATE,'28-
-------------------
28/09/2009 18:36:48

1 row selected.

SQL> select decode(sysdate, '28-SEP-09', null, sysdate) from dual
  2  /
DECODE(SYSDATE,'28-
-------------------
28/09/2009 18:37:05

1 row selected.

None of them give me NULL.
NEVER relies on implicit conversion.

NULLIF is still better as it EXACTLY expresses what OP wants:
SQL> select nullif(trunc(sysdate),to_date('28/09/2009','DD/MM/YYYY')) from dual;
NULLIF(TRUNC(SYSDAT
-------------------


1 row selected.

Regards
Michel
Re: Convert 1/1/1900 to NULL [message #423870 is a reply to message #423852] Mon, 28 September 2009 16:38 Go to previous message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Quote:
NEVER relies on implicit conversion.

Yes and this was my point. She needs to have a to_date for it to behave as she wants. And that also goes for nullif, as you also use in your example now. (Although in your first reply you just gave a link to the docs for nullif, so I thought I would throw in the to_date issue.) The decode examples I gave was to demonstrate the different results which could arise with no to_date. My results are of course based on my setup.
Sorry if I didn't stress the to_date part hard enough...
Previous Topic: ORA_ROWSCN NUll for first row
Next Topic: Variables in SQL Query
Goto Forum:
  


Current Time: Sun Dec 11 08:11:04 CST 2016

Total time taken to generate the page: 0.13538 seconds