Home » SQL & PL/SQL » SQL & PL/SQL » how to handle null in date time
how to handle null in date time [message #574593] Sun, 13 January 2013 23:55 Go to next message
anniepeteroracle
Messages: 43
Registered: March 2012
Location: hyderabad
Member
create table top22 (timed timestamp);

insert into top22(timed)
values (current timestamp),(null);

select timed from top22;

2013-01-14 10:50:22.17
NULL


select 'no is '||coalesce("num",'') from "bifrost"."top22"

getting error in DB2

if i remove this coalesce function
select 'no is '||timed from "bifrost"."top22"
no is 2013-01-14 10:50:22.17
NULL

but i need the second result is no is null
Re: how to handle null in date time [message #574594 is a reply to message #574593] Mon, 14 January 2013 00:13 Go to previous messageGo to next message
anniepeteroracle
Messages: 43
Registered: March 2012
Location: hyderabad
Member
how we can handle in oracle ?

for NVL functions in date data type
Re: how to handle null in date time [message #574596 is a reply to message #574593] Mon, 14 January 2013 00:30 Go to previous messageGo to next message
anniepeteroracle
Messages: 43
Registered: March 2012
Location: hyderabad
Member
thanks

I got the answer ...
Re: how to handle null in date time [message #574597 is a reply to message #574596] Mon, 14 January 2013 00:32 Go to previous messageGo to next message
BlackSwan
Messages: 23150
Registered: January 2009
Senior Member
please post the answer
Re: how to handle null in date time [message #574602 is a reply to message #574593] Mon, 14 January 2013 01:16 Go to previous messageGo to next message
anniepeteroracle
Messages: 43
Registered: March 2012
Location: hyderabad
Member

select 'no is '||coalesce("num",'') from "bifrost"."top22"



this is wrong query

the correct query is

select 'no is '||coalesce(cast(timed as varchar2(30)),'')) from top22;

no is 2013-01-14-10.50.22.170000
no is


...

[Updated on: Mon, 14 January 2013 01:17]

Report message to a moderator

Re: how to handle null in date time [message #574603 is a reply to message #574602] Mon, 14 January 2013 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is wrong too and even VERY wrong.
And what is the purpose of your COALESCE call if your second parameter is NULL?

In addition, Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.


Regards
Michel

[Updated on: Mon, 14 January 2013 01:19]

Report message to a moderator

Re: how to handle null in date time [message #574604 is a reply to message #574603] Mon, 14 January 2013 01:29 Go to previous messageGo to next message
anniepeteroracle
Messages: 43
Registered: March 2012
Location: hyderabad
Member
so what is the correct answer ?
please help to find out ,
actually this code is related to db2 .
Re: how to handle null in date time [message #574605 is a reply to message #574604] Mon, 14 January 2013 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The correct answer depends on what OP wants when the date is null and he didn't tell us.
You can use NVL or COALESCE, but you must NOT use CAST but TO_CHAR.
This is an Oracle forum, so please post Oracle solutions not db2 ones; and post them formatted.

Regards
Michel
Re: how to handle null in date time [message #574606 is a reply to message #574605] Mon, 14 January 2013 01:35 Go to previous messageGo to next message
anniepeteroracle
Messages: 43
Registered: March 2012
Location: hyderabad
Member
okay thanks
Re: how to handle null in date time [message #574607 is a reply to message #574605] Mon, 14 January 2013 01:59 Go to previous message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but you must NOT use CAST but TO_CHAR.


I should add: if you want a specific format otherwise it is not necessary:
SQL> drop table t;

Table dropped.

SQL> create table t (v date);

Table created.

SQL>  insert into t values (null);

1 row created.

SQL> insert into t values (to_date('13/11/1956','DD/MM/YYYY'));

1 row created.

SQL> commit;

Commit complete.

SQL> select 'Date is: '||nvl(v, sysdate) r from t;
R
----------------------------
Date is: 14/01/2013 09:18:48
Date is: 13/11/1956 00:00:00

2 rows selected.

SQL> select 'Date is: '||to_char(nvl(v, sysdate),'DD-MON-YYYY') r from t;
R
--------------------
Date is: 14-JAN-2013
Date is: 13-NOV-1956

SQL> select 'Date is: '||nvl(to_char(v,'DD-MON-YYYY'),'Unknown') r from t;
R
--------------------
Date is: Unknown
Date is: 13-NOV-1956

You can replace NVL by COALESCE in the same way.

Regards
Michel

[Updated on: Mon, 14 January 2013 02:19]

Report message to a moderator

Previous Topic: Your kindly help in this query (pivot)
Next Topic: can we tune this query without creating any objects like indexes etc.
Goto Forum:
  


Current Time: Fri Dec 19 15:17:11 CST 2014

Total time taken to generate the page: 0.17065 seconds