How sysdate works? [message #426583] |
Fri, 16 October 2009 14:11  |
abhishek_510
Messages: 24 Registered: October 2009
|
Junior Member |
|
|
When I run the following query it gives the output of dbms_output.put_line (l) as 17-oct-2009.It is not giving the timestamp. But when I am inserting l into the table it is getting inserted as date with timestamp. So how the sysdate function works?? Why it is not giving timestamp in the output? If I run the query select * from ab_test where creation_date <= l then the what value of l would be taken here- with timestamp or without timestamp???
begin
l := sysdate ;
dbms_output.put_line (l);
insert into ab_test values (l);
commit ;
end ;
The table contains only one column "creation_date" whose data type is DATE.
|
|
|
Re: How sysdate works? [message #426584 is a reply to message #426583] |
Fri, 16 October 2009 14:16   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
sysdate always includes the time part, you just don't see it because your default date format (NLS_DATE_FORMAT) doesn't seem to include the time part.
If you want do display it in another format use the to_char function with the format you want to see.
|
|
|
|
|
Re: How sysdate works? [message #426689 is a reply to message #426583] |
Mon, 19 October 2009 00:39   |
abhishek_510
Messages: 24 Registered: October 2009
|
Junior Member |
|
|
Actually i am having problem with two cursors. The old one was comparing date like Cursor abhi_test IS
SELECT DISTINCT.....
where ( ( table.end_date_active IS NULL OR table.end_date_active >= l_sysdate )
AND ( table.start_date_active IS NULL OR table.start_date_active <= l_sysdate )).....
here end_date_active or start_date_active contains only date in table. And we are assigning l_sysdate := sysdate.
the new cursor is changed. It is like
l_cursor := DBMS_SQL.OPEN_CURSOR;
l_dyn_str :=
' SELECT '||
' DISTINCT ' ||...
' WHERE ' ||
( ( table.end_date_active IS NULL OR table.end_date_active >= :b1_sysdate ) AND ' ||
' ( table.start_date_active IS NULL OR table.start_date_active <= :b2_sysdate ) ' ||...
DBMS_SQL.PARSE ( l_Cursor, l_dyn_str, DBMS_SQL.NATIVE );
DBMS_SQL.BIND_VARIABLE (l_cursor, ':b1_sysdate', l_sysdate, 32767);
DBMS_SQL.BIND_VARIABLE (l_cursor, ':b2_sysdate', l_sysdate, 32767);
...
Now the scenario is i have two records in table. For one the start_date_active is todays date(date only its doesn't have the timestamp) and end_date_active is null. For 2nd record i have start_date_active is yesterday's date and end_date_active is todays date.
now the problem is the old cursor is fetching only one row and new cursor is fetching two rows.
I tested the whole cursor and i am sure this problem is only depending on the date condition.
Why the 2nd one is giving 2 rows?? I dont have much idea about this dbms_sql.open_cursor and those things.
|
|
|
|
|
|
|
|
|
|
Re: How sysdate works? [message #426714 is a reply to message #426583] |
Mon, 19 October 2009 02:11   |
abhishek_510
Messages: 24 Registered: October 2009
|
Junior Member |
|
|
So there are two ways to fix it...either we can put sysdate inside the query or change the default date format....am i right??
And Michel...can you please explain me how this bind variable works?? or you can suggest me some ebook/materials from where i can study about them...
thanks
|
|
|
|
|
Re: How sysdate works? [message #426734 is a reply to message #426732] |
Mon, 19 October 2009 04:05   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Because when you insert the value SYSDATE into a column, it inserts the current date and time.
when you select that column using a default date mask that only shows the date, it does not display the time part of the date. The time part is still there in the databse, but it's just not getting displayed.
|
|
|
|