Home » SQL & PL/SQL » SQL & PL/SQL » How sysdate works? (Oracle)
How sysdate works? [message #426583] Fri, 16 October 2009 14:11 Go to next message
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 Go to previous messageGo to next message
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 #426670 is a reply to message #426584] Sun, 18 October 2009 13:35 Go to previous messageGo to next message
abhishek_510
Messages: 24
Registered: October 2009
Junior Member
so if i try to compare a date with l then would l be compared as a date only or a date with timestamp?
Re: How sysdate works? [message #426671 is a reply to message #426670] Sun, 18 October 2009 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Oracle dates always contain time part, so if you compare 2 dates fields/variables you compare them including the time part.

Regards
Michel
Re: How sysdate works? [message #426689 is a reply to message #426583] Mon, 19 October 2009 00:39 Go to previous messageGo to next message
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 #426693 is a reply to message #426583] Mon, 19 October 2009 00:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Why the 2nd one is giving 2 rows?? I dont have much idea about this dbms_sql.open_cursor and those things.
I give up.
How can anyone reproduce what you report?


Re: How sysdate works? [message #426696 is a reply to message #426583] Mon, 19 October 2009 01:09 Go to previous messageGo to next message
abhishek_510
Messages: 24
Registered: October 2009
Junior Member
well...is there any chance that l_sysdate is taking sysdate with time in the first cursor and in the 2nd it is taking only date??
Re: How sysdate works? [message #426698 is a reply to message #426696] Mon, 19 October 2009 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
How l_sysdate is declared and filled?
What are the datatypes of %_date_active columns?

Regards
Michel
Re: How sysdate works? [message #426699 is a reply to message #426583] Mon, 19 October 2009 01:21 Go to previous messageGo to next message
abhishek_510
Messages: 24
Registered: October 2009
Junior Member
l_sysdate DATE;
l_sysdate := SYSDATE;
datatypes of %_date_active columns are DATE
Re: How sysdate works? [message #426702 is a reply to message #426699] Mon, 19 October 2009 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why don't you put sysdate inside the query? You will then have no problem from bind variables.

Regards
Michel
Re: How sysdate works? [message #426704 is a reply to message #426583] Mon, 19 October 2009 01:37 Go to previous messageGo to next message
abhishek_510
Messages: 24
Registered: October 2009
Junior Member
Yeah....but the problem is i cannot change the code Sad ...It has to be as it is. My job is to find out the reason why it is happening. Why the bind variable is not taking the time with date?
Re: How sysdate works? [message #426706 is a reply to message #426704] Mon, 19 October 2009 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The reason is simple and if you want to fix it you have to change the code.
The reason is that as you use BIND_VARIABLE function in its IN OUT form, the parameter is of VARCHAR2 datatype and so there is an implicit conversion and as your default date format does not contain the time part, the implicit conversion only keeps the date.

Regards
Michel
Re: How sysdate works? [message #426714 is a reply to message #426583] Mon, 19 October 2009 02:11 Go to previous messageGo to next message
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 #426717 is a reply to message #426714] Mon, 19 October 2009 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There are other ways, for instance use BIND_VARIABLE function without the last parameter, then you call it with the correct datatype.

Application Developer's Guide - Fundamentals

Regards
Michel

[Updated on: Mon, 19 October 2009 02:21]

Report message to a moderator

Re: How sysdate works? [message #426732 is a reply to message #426583] Mon, 19 October 2009 04:00 Go to previous messageGo to next message
abhishek_510
Messages: 24
Registered: October 2009
Junior Member
Thanks Michel.... Smile
But can you explain one thing....when i was printing l it was giving only date because of the default date format but when i was inserting it into ab_test why it was getting inserted as date with time?
Re: How sysdate works? [message #426734 is a reply to message #426732] Mon, 19 October 2009 04:05 Go to previous messageGo to next message
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.
Re: How sysdate works? [message #426739 is a reply to message #426583] Mon, 19 October 2009 04:26 Go to previous message
abhishek_510
Messages: 24
Registered: October 2009
Junior Member
ok...i get it...thanks
Previous Topic: GroupBy
Next Topic: Create View with last 10080 records
Goto Forum:
  


Current Time: Fri Feb 14 14:39:09 CST 2025