Oracle date format issue

>>When the dates are compared in SqlPlus, they are comparing dates without timestamps

Not sure this is possible.

Can you provide a test case with sample data that shows this is happening?

The nls_date_format parameter just determines how dates are displayed by default. When assigning sysdate to a date variable, there is definitely a time component present. So when comparing a date data type to that variable, it's highly unlikely (if not impossible) that they would ever be equal.

>>> What can be done to fix the issue?

use TRUNC on both sides of the comparison, that will still compare time values, but both trunc results will be 00:00:00 for the time portion

What you "see" for a date is NOT what is stored. The date data type in Oracle is actually stored as a set of integers, not at all like anything we humans would understand as a date or time.

What we humans want/need to see as a date is a string, usually in our favourite sequence that our brains have been trained to understand.
For example when I see "12/12/2015" it is the "12th of December 2015"
but you might "see it" differently.

So. It simply does not matter what your "date format" is.

SYSDATE is both the date and time, always.
You can use a function like TRUNC() to amend the value (but it just sets the time to 00:00:00)

Try this

select
      to_char(sysdate,'yyyy-mm-dd HH24:mm:ss')
    , to_char(TRUNC(sysdate),'yyyy-mm-dd HH24:mm:ss')
from dual;

also try looking at you data in a similar fashion:

select
      to_char(DATE_COL,'yyyy-mm-dd HH24:mm:ss')
    , to_char(TRUNC(DATE_COL),'yyyy-mm-dd HH24:mm:ss')
from thetable
where rownum < 11;

The point of this is that you can "see" date/time information in many different ways, but it is not relevant to how they are stored or how to compare what is stored to some variable.

>>When the dates are compared in SqlPlus, they are comparing dates without timestamps even though both dates have timestamp components. I confirmed this by doing a DBMS_OUTPUT.PUT_LINE in SqlPlus.<<
What did your dbms_output.put_line look like?

I did a dbms_output on DATE_COl. Today, it is giving the value with the timestamp whereas before it wasn't. Still researching.

Again, what did your exact dbms_output line look like? What is the data type of DATE_COL? What does the following query show? select name, value from v$parameter where name like 'nls%format';

I am getting a message saying 'table or view does not exist' for v$paramater. Do I have to have to DBA rights to query it?

you need to have select privilege on it but you do not need DBA privileges to read it.

Here is out put from 'select name, value from v$parameter where name like 'nls%format':

Name                                          Value
----------                                       -----------
nls_date_format                      DD-MON-RR
nls_time_format                      HH.MI.SSXFF AM
nls_timestamp_format           DD-MON-RR HH.MI.SSXFF AM
nls_time_tz_format                 HH.MI.SSXFF AM TZR
nls_timestamp_tz_format      DD-MON-RR HH.MI.SSXFF AM TZR

>>When the dates are compared in SqlPlus, they are comparing dates without timestamps

I'm still suggesting that what you explain as far as comparing dates using sqlplus is not possible in Oracle using a script.

Now if you are just observing the output of something, then yes, it is possible,  You are comparing the string representation of a DATE not the actual date value.

Please confirm the column data type is DATE.

>>Today, it is giving the value with the timestamp whereas before it wasn't.

Many things can do this.  Probably the NLS_DATE_FORMAT.  If this was set to something different then the times may or may not be displayed.  This has nothing to do with comparisons.

You can find all the allowed formats masks here:
http://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212

To show this please run the following test case:

begin
dbms_output.put_line(sysdate);
end;
/

alter session set nls_date_format='DD MM YYYY';

begin
dbms_output.put_line(sysdate);
end;
/

alter session set nls_date_format='Day Month DD YYYY';
begin
dbms_output.put_line(sysdate);
end;
/

alter session set nls_date_format='HH24:MI:SS';
begin
dbms_output.put_line(sysdate);
end;
/

Select allOpen in new window

I hope this will help.
When Oracle stores a date or timestamp it is NOT stored "in a format"

Dates or Timestamps are stored as sets of NUMBERS, if the 'date' data type it is precise to a second, or if 'timestamp' then precise up to 9 decimal places of a second (default is 6)

(nb. storing as numbers is why databases can do things like date arithmetic)

When we humans want to look at the stored data we need the database to send us strings that our brains understand as dates/times. However human culture now gets in the road. For example if your are French then "January" is "Janvier"; if an Australian we want to see dd/mm/yyyy if European you might want yyyy.mm.dd and if American may prefer mm/dd/yyyy and so on. These are "formats" and there are default formats.

So it is through these formats that we "see" the data; But the actual data isn't what we look at.

slightwv, the declaration is:
declare DATE_COL date := sysdate;

I added a dbms_output.put_line(DATE_COL) in the script. When I execute the script, the value SqlPlus spits out is:
15-JUN-16

I don't have a problem when the display in Toad is different. I am executing this script from another tool (data warehousing) and that tool is requiring that I do a TRUNC before doing the compare to get the same results as the script running from SqlPlus.
--------------------------------------------------------------------------------------------------------------------------------------
slightvw, here are the outputs to your SQLs:

begin
dbms_output.put_line(sysdate);
end;
/
Output from SqlPlus: 15-JUN-16

alter session set nls_date_format='DD MM YYYY';

begin
dbms_output.put_line(sysdate);
end;
/
Output from SqlPlus: 15 06 2016

alter session set nls_date_format='Day Month DD YYYY';
begin
dbms_output.put_line(sysdate);
end;
/
Output from SqlPlus: Wednesday June       15 2016

alter session set nls_date_format='HH24:MI:SS';
begin
dbms_output.put_line(sysdate);
end;
/
Output from SqlPlus: 16:37:27

All outputs are same from Toad as well. Please let me know if I can answer any more questions. This is really baffling to me.

I knew what the output of that script would be.  I was just using it to show you that dates are dates and they only have a format when converted to strings.

>>before doing the compare to get the same results as the script running from SqlPlus

If it is comparing dates to dates and not strings to strings, I'm still saying it is pretty much impossible.

Please post the script.  You still haven't posted how the compare is being done.

Can you also post the other tool's name and version?

We definitely need to see what the compare statement is.
>>declare DATE_COL date := sysdate;<< There is a time element here even if it shows as 15-JUN-16
If you're trying to compare it to, say, to_date('15-JUN-16','DD-MON-RR'), it will not equate.

I have the same problem in two situations: one compare and the other UPDATE. If I do the UPDATE from SqlPlus, it is putting in a date without timestamp. If I do it from Toad by just copying the SQL from SqlPlus into Toad, it puts the timestamp.

>>If I do the UPDATE from SqlPlus, it is putting in a date without timestamp.

Not possible.

As I asked above:
Can you provide a test case with sample data that shows this is happening?

As mentioned above: Dates in Oracle always have a timestamp.  The timestamp might be zeros but the time portion is there.

Some additional info. I took the whole script to Toad. It updated without the timestamp. I just took the UPDATE statement out, substituted some values and ran in Toad again. It updated with the timestamp. Here is the script that was run in SqlPlus and Toad and updated without the timestamp:

declare
vDATE_COL date := sysdate;

begin
          execute immediate 'update Tbl1 set (Col1, DateCol)
          =select Col1, ''' || vDATE_COL || ''' from Tbl1 where Col2=Col3';

        commit;
end;

NOTE:
a) UPDATE is happening on the same table on which the SELECT is happening.
b) The script is more complex than this but I am showing the problem column, vDATE_COL. I'll post the whole script if needed.

And while I'm thinking about it, why are you selecting from the same table as part of the update?

Can you not just update the columns you want without the select?

Why are you updating col1 from tbl1 setting it to select co1 from tbl1?
Whenever I'm issuing an execute immediate, I like to do like the following first.
DECLARE
vDATE_COL date := sysdate;
vSQL varchar2(255);  ==> or however many characters you think the update statement needs
BEGIN
vSQL := 'update tbl1 set DateCol = '||vDATE_COL||' where col2 = col3';
dbms_output.put_line(vSQL);
--execute immediate vSQL;
END;
/
Run that the first time to see what vSQL looks like and, if it's what you want, then repeat commenting out the dbms_output statement and un-commenting the execute immediate statement.

I'm still not seeing the need for dynamic SQL

Just do:
DECLARE
    vDATE_COL date := sysdate;
begin
       update tbl1 set DateCol = vDATE_COL where col2 = col3;
end;
/

Then you don't have implicit date to string then back to date conversions.  Then NLS_DATE_FORMAT doesn't matter.

Much simpler...

I have a question regarding the compare. My v$parameter values are like I posted earlier (nls_date_format: DD-MON-RR).

I tried this SQL in Toad:

declare vDATE_COL date := sysdate;
vDATE_COL2 date;

begin
execute immediate 'UPDATE Tbl1 SET Col1 = :1 WHERE Col2=1' USING vDATE_COL;
select Col1 into vDATE_COL2 from Tbl1 where Col2=1;
dbms_output.put_line(vDATE_COL2);
end;

dbms_output displays a date in this format: 14-JUN-2015
When I run this query:
select Col1 from Tbl1 where Col2=1
I get a date with timestamp. Do you know why?

My guess is Toad has it's own default date format mask that it applies to the select but cannot apply the mask in the PL/SQL block because dbms_output.put_line displays a string so the date to string conversion is done before Toad gets it.

It's a Guess because I don't use Toad or a GUI.  If it is repeatable in sqlplus, then I can help...

I created the script below. I ran the script in SqlPlus:

declare vDATE_COL date := sysdate;
vDATE_COL2 date;

begin
execute immediate 'UPDATE Tbl1 SET Col1 = :1 WHERE Col2=1' USING vDATE_COL;
commit;
select Col1 into vDATE_COL2 from Tbl1 where Col2=1;
dbms_output.put_line(vDATE_COL2);
end;

This is the result I get without a timestamp:
18-JUN-16

When I run the following query again from the SqlPlus prompt:
SQL>select Col1 from Tbl1 WHERE Col2=1;

I get the result:
18-JUN-16

again, no timestamp.

>>again, no timestamp.

Again, NLS_DATE_FORMAT.

sqlplus is bound by that setting.

I'm saying Toad shows the time because it has it's own format mask when it sees a date.

If you want to see the times in sqlplus either set NLS_DATE_FORMAT to a mask that has the times or use TO_CHAR with a mask that has the times.

>>,,,
select Col1 into vDATE_COL2 from Tbl1 where Col2=1;
 dbms_output.put_line(vDATE_COL2);
 end;

 This is the result I get without a timestamp:
 18-JUN-16<<
This result includes a time element since sysdate always has a time element, it's just that it gets displayed in nls_date_format (apparently DD-MON-RR)

>>When I run the following query again from the SqlPlus prompt:
 SQL>select Col1 from Tbl1 WHERE Col2=1;

 I get the result:
 18-JUN-16<<
This result may will also contain a time element. Depending on how Col1 was populated that element could be 00:00:00 or an actual time. Again, it's being displayed in nls_date_format.

If you're trying to compare dates with time elements you can do that directly with where date1 = (or < or >, etc.) date2.
If you're trying to compared dates regardless of their time element, then you could use where trunc(date1) = (or < or >, etc.) trunc(date2), where the trunc(date) function which in essence sets the time element to 00:00:00 for both dates.

Sorry, this sentence "This result may will also contain a time element." should have read "This result will also contain a time element."

So going back to my original question.

declare vDateVar DATE := sysdate;
UPDATE Tbl1
set Col2='Abc'
WHERE DATE_COL <> vDateVar;

Are you saying that this comparison happens using nls_date_format of DD-MON-RR? That is only date parts of both dates are used during comparison even though both have a timestamp component?

if date_col is actually a date, then no,  the nls_date_format does not apply  the time component of both values will be used in the evaluation.

if date_col is actually text (that simply looks like a human readable date) then yes, nls_date_format will apply because one of the two values will need to be converted to the format of the other.

DATE_COL is of datatype date. It is updated earlier using a SQL like this:
execute immediate 'UPDATE Tbl1 set DATE_COL=:1 WHERE Col2=1' USING vDateVar;

vDateVar is declared as:
declare vDateVar DATE := sysdate;

It seems like when I am doing this update from the other tool (Datastage 9.1) it uses timestamp whereas from SqlPlus it doesn't. So I haven't really found an answer to my original question. I would think that it would use the same nls_date_format value.

>>> DATE_COL is of datatype date. It is updated earlier using a SQL like this:

that doesn't prove that it's of date type.  

execute immediate 'UPDATE Tbl1 set DATE_COL=:1 WHERE Col2=1' USING vDateVar;

why would you do that?  There is nothing dynamic about that tatement, why use execute immediate?
that's not your date problem, but not a good way to write that code either.

you haven't show what you're doing.  Post the actual sqlplus output.  screen shot if you must for toad , but copy paste of the screen text would be better for sql*plus.

I know you've attempted to describe what you're doing, but either you have a encountered a really gross obvious bug in Oracle (which seems unlikely) or there is something missing you're not telling us.

So, show us the entirety of what you're running.

What exactly are you doing?

You have two pretty different updates in as many posts:
UPDATE Tbl1 set Col2='Abc' WHERE DATE_COL <> vDateVar;
UPDATE Tbl1 set DATE_COL=:1 WHERE Col2=1


Set up a simple test case and try things out:

alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

drop table tab1 purge;
create table tab1(date_col date, col2 varchar2(10));

insert into tab1 values(to_date('01/01/2001 12:00:00','MM/DD/YYYY HH24:MI:SS'),'1');
insert into tab1 values(to_date('01/01/2001 12:00:01','MM/DD/YYYY HH24:MI:SS'),'1');
commit;

declare
	vDateVar DATE := to_date('01/01/2001 12:00:00','MM/DD/YYYY HH24:MI:SS');
begin
	UPDATE tab1 set Col2='Abc' WHERE DATE_COL <> vDateVar;
end;
/

select * from tab1; 
rollback;

declare
	vDateVar DATE := to_date('01/01/2001 12:00:00','MM/DD/YYYY HH24:MI:SS');
begin
	execute immediate 'UPDATE tab1 set DATE_COL=:1 WHERE Col2=''1''' USING vDateVar;
end;
/

select * from tab1; 

Select allOpen in new window

I think we need to step back and before we get to how you are comparing and updating.

I really think we need to get you to understand the core differences between a DATE and a STRING.

Based on the actions you tell Oracle to do, you might be working with a DATE or you might be working with a STRING.  Until you understand which is which, I don't think you'll get the answer you need.

Once you understand the difference, you likely won't need the answer to this question either.

Sysdate is of data type "date"
In Oracle that data type is accurate to the second
So sysdate is always the date and time accurate to the second

If you set a "date" type column equal to sysdate that column now contains the date and time accurate to the second.

If you just select it with no specified output mask then what you see for that value is controlled by nls_date_format

Next time try specifying the output mask

Select to_char(date_col,'yyyy-mm-dd hh:mm:ss')

{+edit} several errors corrected, sorry, didn't have a full keyboard at the time.

>>Are you saying that this comparison happens using nls_date_format of DD-MON-RR?<<
Absolutely not. All date data types have a time element and date comparisons have nothing to do with formats. The link below illustrates how Oracle dates are stored and it's these values that are used for comparing dates.

http://www.ixora.com.au/notes/date_representation.htm

Thanks. This was the cause of my problem.