Thanks for the question, Hassan.

Asked: October 15, 2000 - 11:25 am UTC

Last updated: February 23, 2012 - 7:18 pm UTC

Version: 7.3

Viewed 10K+ times! This question is

You Asked

Having just finished doing my Oracle developer track and now preparing for the OCP certification, I'm developing a small database. I'm having problems regarding the DATE datatype. Is it possible that we can change the default DATE datatype i.e., DD-MM-YY, specially when we are creating a table and similarly when we are populating the Date column, is it possible if we can enter the date entries in a different format like 23/12/00?

and Tom said...

What you want to change is the default date mask for a date -- not the "date datatype" itself.

By default, the default format is either DD-MON-YY or in 8.1.6 and up, it is DD-MON-RR.

You can change this at the session level via an ALTER SESSION:

ops$tkyte@ORA8I.WORLD> create table t ( x date );
Table created.

ops$tkyte@ORA8I.WORLD> alter session set nls_date_format = 'dd/mm/rr';

Session altered.

ops$tkyte@ORA8I.WORLD> insert into t values ( '23/12/00' );
1 row created.

ops$tkyte@ORA8I.WORLD> select x, to_char(x,'dd-mon-yyyy') from t;

X TO_CHAR(X,'
-------- -----------
23/12/00 23-dec-2000

Now, you can do it at the database level in the init.ora parameter but this won't work in general (changing at the session level in your application is suggested). See
</code> http://asktom.oracle.com/Misc/NLSDateFormat.html <code>
for why this is.

I would suggest using a 4 digit century+year if at all possible. Also, read up on the difference between RR and YY to make sure you understand that:

ops$tkyte@ORA8I.WORLD> create table t ( x date );

Table created.

ops$tkyte@ORA8I.WORLD> alter session set nls_date_format = 'dd/mm/rr';

Session altered.

ops$tkyte@ORA8I.WORLD> insert into t values ( '23/12/99' );

1 row created.

ops$tkyte@ORA8I.WORLD> alter session set nls_date_format = 'dd/mm/yy';

Session altered.

ops$tkyte@ORA8I.WORLD> insert into t values ( '23/12/99' );

1 row created.

ops$tkyte@ORA8I.WORLD> select x, to_char(x,'dd-mon-yyyy') from t;

X TO_CHAR(X,'
-------- -----------
23/12/99 23-dec-1999
23/12/99 23-dec-2099

Rating

(43 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Trigger after logon

Marcio, January 30, 2004 - 5:58 am UTC

Tom, Is possible to keep track a connection since logon untill prompt SQL > ?
Explain:
I used your solution </code> http://asktom.oracle.com/~tkyte/Misc/NLSDateFormat.html <code>-- 9i works well, but in 8i doesn't. I would like to know about track, I used this technique:

ops$t_mp00@MRP817> create table t ( x varchar2(45) );

Table created.

ops$t_mp00@MRP817>
ops$t_mp00@MRP817> create or replace trigger after_logon
2 after logon on database
3 begin
4 insert into t select 'Before ' || sysdate from dual;
5 execute immediate 'alter session set nls_date_format = ''dd/mm/yyyy, hh24:mi''';
6 insert into t select 'After ' || sysdate from dual;
7 end;
8 /

Trigger created.

ops$t_mp00@MRP817>
ops$t_mp00@MRP817> connect /
Connected.
ops$t_mp00@MRP817>
ops$t_mp00@MRP817> select t.*, sysdate from t;

X SYSDATE
--------------------------------------------- --------
Before 30/01/04 30/01/04
After 30/01/2004, 08:54 30/01/04

2 rows selected.

But in 9i works dandy!

ops$t_mp00@MRP920> create table t ( x varchar2(45) );

Table created.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> create or replace trigger after_logon
2 after logon on database
3 begin
4 insert into t select 'Before ' || sysdate from dual;
5 execute immediate 'alter session set nls_date_format = ''dd/mm/yyyy, hh24:mi''';
6 insert into t select 'After ' || sysdate from dual;
7 end;
8 /

Trigger created.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> connect /
Connected.
ops$t_mp00@MRP920>
ops$t_mp00@MRP920> select t.*, sysdate from t;

X SYSDATE
--------------------------------------------- -----------------
Before 30/01/04 30/01/2004, 08:54
After 30/01/2004, 08:54 30/01/2004, 08:54

There would be something replace my nls_date_format after firing trigger, but I don't know how to keep track this.

January 30, 2004 - 8:27 am UTC

looks like it is working -- it is just that you have to physically break the connection to the database - not just re-log in in 8i:

ops$tkyte@ORA817DEV> create or replace trigger after_logon
  2  after logon on database
  3  begin
  4     insert into t select 'Before ' || sysdate from dual;
  5     execute immediate
  6     'alter session set nls_date_format = ''dd/mm/yyyy, hh24:mi''';
  7     insert into t select 'After ' || sysdate from dual;
  8  end;
  9  /
 
Trigger created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> connect /
Connected.
ops$tkyte@ORA817DEV> select sysdate from dual;
 
SYSDATE
---------
30-JAN-04
 
ops$tkyte@ORA817DEV> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
 
Fri Jan 30 08:34:33 EST 2004
(tkyte@aria-dev) /export/home/tkyte
> plus
 
SQL*Plus: Release 8.1.7.0.0 - Production on Fri Jan 30 08:36:10 2004
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
 
ops$tkyte@ORA817DEV> select sysdate from dual;
 
SYSDATE
-----------------
30/01/2004, 08:36
 
ops$tkyte@ORA817DEV>

RE: Trigger after logon

Marcio, January 30, 2004 - 3:37 pm UTC

You're right, but it replaces with default DD/MON/YY as soon as you reconnect inside of sql*plus...

ops$t_mp00@MRP817> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

c:\migracao\loads>sqlplus /

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Jan 30 18:30:16 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

ops$t_mp00@MRP817> select sysdate from dual;

SYSDATE
-----------------
30/01/2004, 18:30

1 row selected.

ops$t_mp00@MRP817> connect /
Connected.
ops$t_mp00@MRP817> select sysdate from dual;

SYSDATE
--------
30/01/04

1 row selected.

Is this an expected behave?
FYI - I've commented my login.sql concern ALTER SESSION SET NLS_DATE_FORMAT, and tried to create trigger with AFTER LOGON ON SCHEMA as well.

January 30, 2004 - 7:59 pm UTC

looks like a sql plus issue. when you connect in sqlplus, it doesn't fire off a new dedicated server.

You'll have to persue this with support at this point (or don't use "connect" i guess in plus). It probably won't affect developed applications which don't disconnect/connect like this.

Bytes for Date ...

reader, February 20, 2004 - 5:13 pm UTC

Hi Tom,

I've a basic question. Date has 7 bytes in 8i and 9 bytes in 9i. What is the additional 2 bytes for? Is it TZM and TZH that count? Please explain.

Thanks in advance.

February 20, 2004 - 7:08 pm UTC

date has 7 bytes in all releases.

TIMESTAMP has 7 to 11 in 9i and up. (and it would be timezone and/or fractional seconds consuming the extra bytes)

reader, February 26, 2004 - 12:31 pm UTC

On 9i, using default nls_date_format, why does this happen ? 

  1* SELECT TO_CHAR(TO_DATE('26/02/03','DD/MM/YYYY'),'DD/MM/YYYY') FROM DUAL
SQL> /

TO_CHAR(TO
----------
26/02/0003

February 26, 2004 - 2:24 pm UTC

the nls_date_format does not come into play here since you are being explicit.

TO_DATE('26/02/03','DD/MM/YYYY')

is the same as

TO_DATE('26/02/0003','DD/MM/YYYY')

is the same as

TO_DATE('26/02/3','DD/MM/YYYY')

is the same as

TO_DATE('26/02/003','DD/MM/YYYY')


  1  select to_date( '1/1/3','dd/mm/yyyy'),
  2         to_date( '1/1/03','dd/mm/yyyy'),
  3         to_date( '1/1/003','dd/mm/yyyy'),
  4         to_date( '1/1/0003','dd/mm/yyyy')
  5*  from dual
ops$tkyte@ORA920PC> /
 
TO_DATE('1/ TO_DATE('1/ TO_DATE('1/ TO_DATE('1/
----------- ----------- ----------- -----------
01-jan-0003 01-jan-0003 01-jan-0003 01-jan-0003
 


leading zeroes on a number are meaningless.  I assume you are asking "why is it the year 3". 

remember it takes no more than and equally no less than 4 characters to represent 2003, 1903, 1803 and so on.  03 is the year 3.

more about dates

A reader, September 02, 2005 - 4:05 am UTC

Hi Tom,
how is the date internally stored in Oracle, I mean whenever we query the database we see it in a format, but how is it exactly stored in the database. Thanks a lot in advance for your help.

Kind Regards,
Your fan.

September 02, 2005 - 6:19 am UTC

very short excerpt from my forthcoming book, chapter on datatypes:


<quote>
DATE Type

The DATE type is a fixed-width 7-byte date/time datatype. It will always contain the seven attributes of the century, the year within the century, the month, the day of the month, the hour, the minute, and the second. Oracle uses an internal format to represent that information, so it is not really storing 20, 05, 06, 25, 12, 01, 00 for June 25, 2005, at 12:01:00. Using the built-in DUMP function, we can see what Oracle really stores:

ops$tkyte@ORA10G> create table t ( x date );
Table created.
 
ops$tkyte@ORA10G> insert into t (x) values
  2  ( to_date( '25-jun-2005 12:01:00',
  3             'dd-mon-yyyy hh24:mi:ss' ) );
1 row created.
 
ops$tkyte@ORA10G> select x, dump(x,10) d from t;
 
X         D
--------- -----------------------------------
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1

The century and year bytes (the 120,105 in the DUMP output) are stored in an excess-100 notation. You would have to subtract 100 from them to determine the correct century and year. The reason for the excess-100 notation is support of BC and AD dates. If you subtract 100 from the century byte and get a negative number, it is a BC date, for example:

ops$tkyte@ORA10G> insert into t (x) values
  2  ( to_date( '01-jan-4712bc',
  3             'dd-mon-yyyybc hh24:mi:ss' ) );
1 row created.
 
ops$tkyte@ORA10G> select x, dump(x,10) d from t;
 
X         D
--------- -----------------------------------
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
01-JAN-12 Typ=12 Len=7: 53,88,1,1,1,1,1

So, when we insert 01-JAN-4712BC, the century byte is 53 and 53  100 = 47, the century we inserted. Because it is negative, we know that it is a BC date. This storage format also allows the dates to be naturally sortable in a binary sense. Since 4712 BC is less than 4710 BC, wed like a binary representation that supports that. By dumping those two dates, we can see that 01-JAN-4710BC is larger than the same day in 4712 BC, so they will sort and compare nicely:
</quote>

great explanation

A reader, September 06, 2005 - 3:13 am UTC

Hi Tom,
good day to you, thanks a lot for making this clear and for the nice explanation you have given, as you have mentioned about your forthcoming book I would like to know more about it, when is it due and what will be the title, also any plans for book on tuning, would like to guidlines from your book on this particular topic.

Kind Regards,
your fan.

September 06, 2005 - 8:18 am UTC

see home page, link to book on amazon and link to three blog entries where we discussed it in detail.

date query

Bhavesh Ghodasara, September 06, 2005 - 8:40 am UTC

hi tom,
I am preparing a report, In this report there is a field name : EMP_CONFIRM_DUE
format of that DATE is dd-mm-yyyy.
now user enters From month-year and To month-year,
E.g. From date : 200511(YYYYMM)
To date : 200601(YYYYMM)
How can i get only emp_confirm_due dates which are between to and from
thanks in advance
BHAVESH..

September 06, 2005 - 8:49 am UTC

you are confusing me.

formats are used upon insert and select to convert strings to dates and dates to strings.

formats are not used to store data - so, to say "the format of that DATE is dd-mm-yyyy" does not compute.

IF you have a date type in the database (they are all stored exactly the same, regardless of the format used to convert them into strings on the way out of the database or the format used to convert a string into a date on the way into the database), then

where date_field >= to_date( :x, 'yyyymm' )
and date_field < add_months(to_date( :y, 'yyyymm' ),1)

when date not store in table

Nopparat V., September 07, 2005 - 1:08 pm UTC

According to your answer on how date internally stored in Oracle, I tested it and found the same result. However, when I tested with then following statement, I saw something make me surprised

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss' ;

Session altered.

SQL> select sysdate, dump(sysdate) from dual ;

SYSDATE
-------------------
DUMP(SYSDATE)
-----------------------------------------------------------------
07/09/2005 23:47:53
Typ=13 Len=8: 213,7,9,7,23,47,53,0

when 'date' is not stored in database, the internal bytes is different, isn't it? I also saw typ=13 ,not typ=12 as when it is stored in table. Could you explain me how the 
first two bytes imply the year 2005?

I also noted that the parts of time (hour, minute and second) is added by 1 when store in table but is not added when not store in table. Could you guide me what is the reason for this additional '1' ?

Thank you in advance

September 07, 2005 - 2:06 pm UTC

I'm only concerned about the physical storage, ignore the "variable" one (sysdate). It is an internal type - special, different.

strange dates

Igor, November 03, 2005 - 10:51 am UTC

Hi Tom,

For some dates I cannot find four-digit year:

SQL> select id,birth_date,to_char(birth_date,'DD-MON-YYYY')
  2  from person
  3  where birth_date < '01-JAN-1900';

        ID BIRTH_DAT TO_CHAR(BIR
---------- --------- -----------
     80445 15-AUG-95 00-000-0000
    292586 20-OCT-96 00-000-0000

Dump looks pretty strange:

SQL> select id,dump(birth_date)
  2  from person
  3  where birth_date < '01-JAN-1900';

        ID
----------
DUMP(BIRTH_DATE)
------------------------------------------------------------------
     80445
Typ=12 Len=7: 45,5,8,15,1,1,1

    292586
Typ=12 Len=7: 45,4,10,20,1,1,1

What format mask should i use for these dates to get four-digit year?

Thanks,
Igor

November 04, 2005 - 2:37 am UTC

You have bad data in there - what inserts/updates this?

A reader, November 04, 2005 - 9:59 am UTC

OK, thanks Tom.

Our developers have written a java GUI application which inserts/updates this data. Probably there is not enough validation of user input in this application, so when a user makes some typo, bad date may be placed into the database. I will recommend them to add more checking on application level.

November 04, 2005 - 5:13 pm UTC

No, if they use normal datatypes (strings for example with to_date) there will be no issue. what are they USING to insert the dates - exact code???

A reader, November 04, 2005 - 12:06 pm UTC

Tom,

I just talked to the developers, they said there is no checking of this date input field on application level. They only rely on Oracle database which is supposed to return an error in case of bad user input. So it looks like in rare cases the database fails to recognize bad input and allows bad dates into it.

This issue is absolutely not critical for us because only two dates from about 50000 are bad so far and they can be easily found and fixed, so there is only theoretical interest about why Oracle sometimes failed to reject bad dates.

In case you need details the database was 9.2.0.6 at the time when these bad dates were inserted, the java application was written using JDeveloper. We cannot reproduce these errors at will because nobody knows what typos were made by end users.

Thanks
Igor

November 04, 2005 - 5:30 pm UTC

let us see the Code.

it is not normally possible to do this - the database would find this (using to_date for example)

A reader, November 07, 2005 - 3:38 pm UTC

//the line below uses oracle business layer
dbRow.setAttribute("BirthDate", MGUIFrame.formatmmddyyyyToyyyymmdd(jTextBirthDate.getText().toString()));

And the function is below:

public static String formatmmddyyyyToyyyymmdd(String textdate) {
if (textdate.equals(""))
return null;
String datestring = null;
try {
// formatting date - convert the date formats MM-dd-yyyy => yyyy-MM-dd
SimpleDateFormat tmpLocalFormat = new SimpleDateFormat();
SimpleDateFormat tmpLocalFormat2 = new SimpleDateFormat();
tmpLocalFormat.applyPattern("yyyy-MM-dd");
tmpLocalFormat2.applyPattern("MM-dd-yyyy");
datestring = new String(tmpLocalFormat.format(tmpLocalFormat2.parse(textdate)));
// formatting done

}
catch (Exception e) {
e.printStackTrace();
}
return datestring;
}// end function

Our developer has sent me this code which converts user input into date format needed for Oracle business layer created by jdeveloper. He said there is no explicit insert/update in this code but it's done by Oracle business layer. He also said that interaction between GUI and the business layer is always in string format and the business layer takes care of the further processing with the database.

November 08, 2005 - 9:40 pm UTC

if that is true, it would seem to be a bug in the business layer. I'll have to recommend support take a look at this to see if there are any known issues.

oracle.sql.TIMESTAMP does not properly convert BC dates to java.sql.Timestamp

August D, April 14, 2006 - 8:02 pm UTC

In database version 10.2, with the latest JDBC driver (v 10.2.0.1) oracle.sql.TIMESTAMP.timestampValue() does not return correct values for BC dates. The date is always off by one year from the expected/actual value when the actual date is in the BC era.

Run the following to create the tables:

CREATE TABLE test_table (
the_timestamp TIMESTAMP
);

INSERT INTO test_table (the_timestamp) VALUES ( SYSDATE )
INSERT INTO test_table (the_timestamp) VALUES ( TO_DATE('0044-03-15 11:23:45 BC', 'YYYY-MM-DD HH24:MI:ss AD') )
INSERT INTO test_table (the_timestamp) VALUES ( TO_DATE('0748-04-21 12:18:58 BC', 'YYYY-MM-DD HH24:MI:ss AD') )

I then tested the JDBC by using this query:

SELECT TO_CHAR(the_timestamp, 'YYYY-MM-DD HH24:mi:ss AD') AS the_chars, the_timestamp, DUMP(the_timestamp, 10) AS the_bytes
FROM test_table
ORDER BY the_timestamp ASC

Notice that for the BC dates, the 'Java Timestamp (via timestampValue())' line is always one year less than the expected value returned by to_char (The 'Testing Date:' line):

Testing Date: 0748-04-21 12:18:58 BC with bytes: Typ=180 Len=7: 93,52,4,21,13,19,59
Timestamp via getObject: oracle.sql.TIMESTAMP@1f5d386
Timestamp via getTimestamp: 0748-04-21 12:18:58 BC
Oracle Timestamp:
Timestamp Bytes (via toBytes()): 93, 52, 4, 21, 13, 19, 59
Java Timestamp (via timestampValue()): 0749-04-21 12:18:58 BC

Testing Date: 0044-03-15 11:23:45 BC with bytes: Typ=180 Len=7: 100,56,3,15,12,24,46
Timestamp via getObject: oracle.sql.TIMESTAMP@132e13d
Timestamp via getTimestamp: 0044-03-15 11:23:45 BC
Oracle Timestamp:
Timestamp Bytes (via toBytes()): 100, 56, 3, 15, 12, 24, 46
Java Timestamp (via timestampValue()): 0045-03-15 11:23:45 BC

Testing Date: 2006-04-14 15:19:43 AD with bytes: Typ=180 Len=7: 120,106,4,14,16,20,44
Timestamp via getObject: oracle.sql.TIMESTAMP@872380
Timestamp via getTimestamp: 2006-04-14 15:19:43 AD
Oracle Timestamp:
Timestamp Bytes (via toBytes()): 120, 106, 4, 14, 16, 20, 44
Java Timestamp (via timestampValue()): 2006-04-14 15:19:43 AD

(I can provide my Java test class if you like)

The bytes returned by TIMESTAMP.toBytes() are always the same as those from dump(the_timestamp, 10) so the error must lie in the toTimestamp() method. Am I right?

April 15, 2006 - 1:03 pm UTC

utilize support for this one - not personally sure if this is a "bug" or not. Not a "date" expert myself. And definitely don't use java timestamp types.

00-000-0000 date

Jonathan Taylor, September 26, 2007 - 9:40 am UTC

Can replicate this invalid date in SQL alone (tested in 10.1):-

SQL> SELECT to_char(to_date('01-sep-2007 PM','dd-mon-yyyy PM'),'dd-mon-yyyy hh24:MI:SS')
  2   FROM dual
  3  ;

TO_CHAR(TO_DATE('01-
--------------------
00-000-0000 00:00:00

(I used PM in this way in an attempt to represent the afternoon of the 1/9/2007, in a similar way that to_date('sep-2007','mon-yyyy') represents September 2007 - the resultant date defaults to the first of the month)

Doing date arithmetic also produced strange results:-

SQL> SELECT to_char(to_date('01-sep-2007 PM','dd-mon-yyyy PM')+(1/24),'dd-mon-yyyy hh24:MI:SS')
  2   FROM dual
  3  ;

TO_CHAR(TO_DATE('01-
--------------------
27-aug-2007 04:00:00

More than four days have been subtracted from 1/9/2007.

SQL> SELECT to_date('01-sep-2007 PM','dd-mon-yyyy PM')  
  2  - to_date ('01-sep-2007','dd-mon-yyyy') day_diff
  3   FROM dual
  4  ;

  DAY_DIFF
----------
    -4.875

September 26, 2007 - 9:42 pm UTC

there is a bug lurking there - thanks for the test case. I will file one:

ops$tkyte%ORA10GR2> SELECT to_date('01-sep-2007 PM','dd-mon-yyyy PM') from dual;

TO_DATE('
---------
01-SEP-07

ops$tkyte%ORA10GR2> alter session set nls_date_format= 'dd-mon-yyyy hh24:MI:SS';

Session altered.

ops$tkyte%ORA10GR2> SELECT to_date('01-sep-2007 PM','dd-mon-yyyy PM') from dual;
ERROR:
ORA-01801: date format is too long for internal buffer



no rows selected

ops$tkyte%ORA10GR2> SELECT to_char(to_date('01-sep-2007 PM','dd-mon-yyyy PM'),'dd-mon-yyyy hh24:MI:SS') FROM dual;

TO_CHAR(TO_DATE('01-
--------------------
00-000-0000 00:00:00

Re: 00-000-0000 date

Michel Cadot, September 27, 2007 - 2:42 am UTC

First, 00-000-0000 date is returned when an invalid internal format is encountered by to_char.
Now, let's see what is this invalid format and why this result in August.
Valid Sept. 1st date is the following one:

SQL> select dump(to_date('01-sep-2007','dd-mon-yyyy')) from dual;
DUMP(TO_DATE('01-SEP-2007','DD-
-------------------------------
Typ=13 Len=8: 215,7,9,1,0,0,0,0

The 2 first bytes (7*256+215)=2007 (I'm on Windows so swap bytes)
Next ones: month, day, hour, minute, second and 0
Now with your input format:

SQL> select dump(to_date('01-sep-2007 PM','dd-mon-yyyy PM')) from dual;
DUMP(TO_DATE('01-SEP-2007PM','DD-
---------------------------------
Typ=13 Len=8: 215,7,9,1,139,0,0,0

We have hour=139
Imagine Oracle internally handles this hour in a signed byte (that goes to negative when you reach 128), this gives:

SQL> SELECT to_char(to_date('01-sep-2007','dd-mon-yyyy')+(139-256)/24,'dd-mon-yyyy hh24:MI:SS') from dual;
TO_CHAR(TO_DATE('01-
--------------------
27-aug-2007 03:00:00

So your result when you add 1 hour.

Definitively a bug in the to_date that can be reproduced in all versions we have from 8.1.7 to 10.2.0.3.

Regards
Michel

September 27, 2007 - 6:59 am UTC

thanks for the followup, I'll file it tomorrow when I get back home - appreciate the investigating - it'll make it easier.

00-000-0000 date

Michel Cadot, November 05, 2007 - 9:44 am UTC

Do you have any news about this.
Maybe a bug number that we can follow?

Regards
Michel

November 06, 2007 - 9:15 am UTC

BugNo: 6473270

it is not published as yet, but that is the number assigned to it.

00-000-0000 date

Michel Cadot, November 06, 2007 - 12:20 pm UTC

Thanks, I will check it from time to time.

Regards
Michel

Invalid date values

A reader, February 22, 2008 - 4:27 pm UTC

We 've found some date columns to contain values dumped as TYP=12 LEN=7: 0,0,44,132,93,64,203 (century and year bytes 0) -- but can't reproduce those values on purpose ...

Do you have any idea ...?

February 22, 2008 - 4:35 pm UTC

look at the application that created them.

how did it insert them, using to_date(:some_string,'fmt')

or by using the internal 7 byte format.

If the latter - they are the culprit - i see that a lot, they try to be "smart" and use the binary format (which is not checked, it just passes through)

A reader, February 23, 2008 - 5:32 am UTC

The application in question didn't change at all -- but "only" its environment ... so maybe it's just a configuration issue.

I'v never even heard about a way to use such "internal binary format" to insert date values.

Could you please provide an example of how to do that (e.g. with 9iR2 and nothing but SQL, PL/SQL) -- just to give a rough idea of what could have happened in our abovementioned case?

And if you are aware of any configuration problem (e.g. NLS settings) leading to such invalid date values as described above your comment on that would be very highly appreciated too.

February 24, 2008 - 11:45 am UTC

not with just sql and plsql.

with sql in any language - sure. Here is a pro*c example I concocted a while ago just to show this happens (usually happens in cobol, C type applications trying to be "cute" and doing the dates themselves)

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2078184919103

Using dbms_stats.convert_raw_value ...

A reader, February 24, 2008 - 4:05 am UTC

... with '00002C845D40CB' as its input didn't reproduce this either but results in the following table column dump: Typ=12 Len=7: 255,100,44,132,93,64,203 ...

February 24, 2008 - 11:54 am UTC

eh?

A reader, February 24, 2008 - 4:05 pm UTC

Well, I just thought that the abovementioned hex value 00 00 2C 84 5D 40 CB would correpond to the observed date column dump (decimal values) TYP=12 LEN=7: 0,0,44,132,93,64,203.

That 's why I've tried to generate such an invalid date column value by providing that hex value as input -- but didn't succeed ...

As opposed to that the following sample produced the expected outcome (values within table columns MyCol1 and MyCol2 are equal):

drop table MyTab ;
create table MyTab( MyCol1 date, MyCol2 date ) ;

insert into MyTab( MyCol1 )
values( to_date( '01.01.2002 12:13:14', 'dd.mm.yyyy hh24:mi:ss' ) ) ;

select dump( MyCol1, 16 ) "Dmp16" from MyTab ;
-- HEX: Typ=12 Len=7: 78,66,1,1,d,e,f

declare
v_dMyCol2 MyTab.MyCol2%type ;

begin
DBMS_Stats.Convert_Raw_Value( '786601010D0E0F', v_dMyCol2 ) ;
update MyTab set MyCol2 = v_dMyCol2 ;
end ;
/

select dump( MyCol1, 16 ) "MyCol1Dmp16",
to_char( MyCol1, 'dd.mm.yyyy hh24:mi:ss' ) "MyCol1",
dump( MyCol2, 16 ) "MyCol2",
to_char( MyCol2, 'dd.mm.yyyy hh24:mi:ss' ) "MyCol2"
from MyTab ;

February 24, 2008 - 4:25 pm UTC

yes, in plsql where dates are dates, strings are strings and x is x - it won't happen

you need a "higher level" API to do it, like C...

A reader, February 25, 2008 - 12:24 am UTC

The "funny" thing is that even the abovementioned approach allows to insert *invalid* data into the date table column ...

Isn't it one of the main responsibilities/purposes of a DBMS to enforce domain integrity?

Checking of valid dates

Henrik Ullerichs, February 25, 2008 - 2:39 am UTC

Funny this should pop up now, I was about to ask a question about this next time I got a chage.

We use the internal Oracle date format for fetching and binding through OCI.

If the date is invalid due to a client program error, the invalid date goes straight into the database.

So far, this appears to work:
Add a check constraint:
alter table MyTable add constraint MyTableCC check (MyDate = to_date(to_char(MyDate, 'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS'));

I have not yet tried it outside a simple test case.

I have a similar issue with character sets (if client and database character sets are equal, say WE8ISO8859P1, invalid characters (7F-9F, where euro and trademark is on Win1252) are inserted into the database, hiding the problem until you need to convert the text, say if you connect using JDBC.

Is there any settings, that can be used to force an error on data entry?
If not, it would be nice if they were made.

February 25, 2008 - 8:01 am UTC

nope, that won't work - there are ways to encode the date so that the check would work but the bits and bytes would be wrong (wrong sort order for example). The excess 100 notation would permit that.

To force an error, do NOT use the internal format.

A reader, February 25, 2008 - 10:39 am UTC

To be honest for a "To force an error, do NOT use the internal format" translates into "Rely on applications accessing the database" -- which (at that level) is a new experience for me ...

Could you please explain the benefits or requirements that opened that "binary gate"?

February 25, 2008 - 1:53 pm UTC

none that I know of. Lost in the annals of time.

Alexander, February 25, 2008 - 11:11 am UTC

Hi Tom,

I have a quick question about storing strings as dates. A developer asked me to run something like this

delete from t where should_be_a_date <= '200712';

The column is a varchar, and it appeared to work as they intended. I would like to know why.

Is it just by accident?

February 25, 2008 - 2:00 pm UTC

ask them what their sessions nls_date_format was.....

implicit conversions use that.

A reader, February 25, 2008 - 2:05 pm UTC

column is a varchar - not date

Alexander, February 25, 2008 - 2:36 pm UTC

Actually I tested it out, so it was my session.

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string

I'm asking because I'm worried that's it's wrong and might remove data they don't mean to.  But if it appears to be working, it's tough to back that up.

Just wondering if it technically works, but is bad practice because of implicit conversions,

OR

A bug waiting to happen, like a query that accidentally returns ordered results with no order by because of the access path.  Thank you.

February 25, 2008 - 2:55 pm UTC

it is a bug waiting to happen

ALL implicit conversions are - change the default mask and bam - it'll do something different.

ops$tkyte%ORA10GR2> create table t ( a number, x date );

Table created.

ops$tkyte%ORA10GR2> alter session set nls_date_format ='yyyymm';

Session altered.

ops$tkyte%ORA10GR2> insert into t values ( 1, '200712' );

1 row created.

ops$tkyte%ORA10GR2> alter session set nls_date_format ='yyyydd';

Session altered.

ops$tkyte%ORA10GR2> insert into t values ( 2, '200712' );

1 row created.

ops$tkyte%ORA10GR2> alter session set nls_date_format ='ddmmyy';

Session altered.

ops$tkyte%ORA10GR2> insert into t values ( 3, '200712' );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select a, to_char(x,'dd-mon-yyyy') from t;

         A TO_CHAR(X,'
---------- -----------
         1 01-dec-2007
         2 12-feb-2007
         3 20-jul-2012

about Date datatype

yaoronghui, February 26, 2008 - 4:36 am UTC

i couldn't create the trigger which name is 'after_logon' with the error 'ORA-01031: you have no enough power' and which system power do i need ?
thanks from

February 26, 2008 - 7:57 am UTC

the only power you need is the power of the documentation:

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#sthref7891

Data Format

Kanda Subbiya, February 26, 2008 - 2:28 pm UTC

Hi Tom,

SELECT to_char(C_date ,'YYYY-MM-DD HH24:MI:SS') dt,DUMP(c_date) dump_dt FROM t;

dt dump_dt
-------------------- -----------------------------------
2007-11-09 16:08:52 Typ=12 Len=7: 120,107,11,9,17,9,53

I am wondering why Hour Minute and Second stored differently(ie +1 added to the value)

I am working on 10.2

February 26, 2008 - 3:13 pm UTC

if you have access to expert oracle database architecture, I describe how the dates are stored in detail in there in the chapter on datatypes.

the short answer: because that is the way we do it.

Query to get bad data in a date field

Kumar, April 09, 2008 - 6:04 pm UTC

What is the sql query to get bad data in a varchar2 type date field. In the table data stored in a varchar2 type column instead of date type column.

e.g. if date filed contains following data
@Z5@)@
@Z5@)@
000000

April 10, 2008 - 10:37 am UTC

.. What is the sql query to get bad data in a varchar2 type date field. ....

hahahahahahaha - thanks for the very very good laugh.

This is such a trick question - because......

Everyone knows there is no such thing as a "date field in a varchar2"

In a varchar2 you store precisely and exactly ONE THING - strings. Dates and Numbers are not stored in strings, only strings are.

So, a date field could NEVER EVER CONTAIN that data, never. So we are done.

tah-dah.

gosh, this is annoying. I don't know why people do this, day after day, year after year, decade after decade. We never ever seem to learn, never.

sigh, big sigh...

ops$tkyte%ORA10GR2> create or replace function is_there_a_date_hiding( p_str in varchar2 ) return number
  2  as
  3      l_date date;
  4  begin
  5      l_date := to_date( p_str, 'fxdd-mon-yyyy' );
  6      return 1;
  7  exception
  8      when others
  9      then
 10          if (sqlcode between -1899 and -1830)
 11          then
 12              return 0;
 13          end if;
 14          RAISE;
 15  end;
 16  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select str, is_there_a_date_hiding( str ) from t;

STR                  IS_THERE_A_DATE_HIDING(STR)
-------------------- ---------------------------
29-feb-2001                                    0
29-feb-2008                                    1
20-feb-08                                      0
12-12-2008                                     0

Insert date

A reader, November 23, 2010 - 11:52 am UTC

Tom,
Please see the following example. I cannot explain why Oracle let me insert a date/time without throwing an error. When AM/PM was missing, Oracle assumed that time was AM. I was expecting Oracle to throw an error. Oracle 10.2.0.3.

SQL> drop table t1;

Table dropped.

SQL> create table t1 (c1 date);

Table created.

SQL> insert into t1 values (to_date('11/11/2010 11:30','mm/dd/yyyy hh:mi PM'));

1 row created.

SQL> alter session set nls_date_format='mm/dd/yyyy hh:mi:ss AM';

Session altered.

SQL> select * from t1;

C1
----------------------
11/11/2010 11:30:00 AM

SQL>

Thanks...

November 23, 2010 - 1:20 pm UTC

formats are very forgiving by default - they make assumptions, provide defaults.

Use FX if you want the FORMAT to be EXACT.

ops$tkyte%ORA11GR2> create table t1 (c1 date);

Table created.

ops$tkyte%ORA11GR2> insert into t1 values (to_date('11/11/2010 11:30','mm/dd/yyyy hh:mi PM'));

1 row created.

ops$tkyte%ORA11GR2> insert into t1 values (to_date('11/11/2010 11:30 PM','mm/dd/yyyy hh:mi PM'));

1 row created.

ops$tkyte%ORA11GR2> insert into t1 values (to_date('11/11/2010 11:30','FXmm/dd/yyyy hh:mi PM'));
insert into t1 values (to_date('11/11/2010 11:30','FXmm/dd/yyyy hh:mi PM'))
                               *
ERROR at line 1:
ORA-01840: input value not long enough for date format


ops$tkyte%ORA11GR2> alter session set nls_date_format='mm/dd/yyyy hh:mi:ss AM';

Session altered.

ops$tkyte%ORA11GR2> select * from t1;

C1
----------------------
11/11/2010 11:30:00 AM
11/11/2010 11:30:00 PM

Insert date

A reader, November 23, 2010 - 2:05 pm UTC

Thanks Tom. I learned something new.

A reader, November 17, 2011 - 9:04 am UTC

Hi Tom,


CASE 1:
========

TOAD
=====
 
 select *
 from nls_database_parameters
 where parameter = 'NLS_DATE_FORMAT';
 
  -->DD-MON-RR
 
 SELECT SYSDATE FROM DUAL;
 
 -->17/11/2011 14:33:17
 
 
 

SQLPLUS:
 =========
 SQL>  select *
  2   from nls_database_parameters
  3   where parameter = 'NLS_DATE_FORMAT';

PARAMETER           VALUE
-------------------------------
NLS_DATE_FORMAT    DD-MON-RR


SQL> select sysdate from dual;

SYSDATE
---------
17-NOV-11


1.Does it mean that the date format we see in the client (toad/sqlplus) depends on client’s default date format not the database default format ?

then where is database default format used ? is it used while storing the dates internally in the database ?



CASE 2:
======

SQL> create table x ( x date );

SQL> insert into x values ('01/jan/10');

1 row created.

SQL> insert into x values ('01/jan/2010');

1 row created.

SQL> insert into x values ('01/01/2010');
insert into x values ('01/01/2010')
                      *
ERROR at line 1:
ORA-01843: not a valid month

SQL> insert into x values (to_date('01/01/2010','dd/mm/yyyy'));

1 row created.

SQL> select x from x;

X
---------
01-JAN-10
01-JAN-10
01-JAN-10


When the DATE value we insert into database is not in 'database default format' - we must use TO_DATE
but in the above case –both dates ('01/jan/10' and '01/jan/2010') were inserted successfully into database with out using TO_DATE format function..
Did client (sqlplus) do something here ? (OR) Can we insert date values either in CLIENT’s default format OR  Database default format with TO_DATE successfully ?

Could you please explain this ?


CASE 3:
To_date function -> to send the date values into the database if its not in ‘default format’ so that the database can understand – what is year.month.date etc.. just to EXPLAIN the things to the database 
To_char -> just to get the date OUT from database as in our desired format ..

These 2 functions DON’T have impact on how database STORES a date value internally in the database.

Am I correct here ?





Many thanks

November 17, 2011 - 7:17 pm UTC

1) the database setting MIGHT be used to default your session setting. Your session setting always trumps the database setting. Toad might be setting it for your session. Use nls_session_parameters

ops$tkyte%ORA11GR2> select * from nls_database_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                DD-MON-RR

ops$tkyte%ORA11GR2> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                dd-mon-yyyy hh24:mi:ss

ops$tkyte%ORA11GR2> select sysdate from dual;

SYSDATE
--------------------
17-nov-2011 20:08:39

better YET (much better yet), never ever rely on defaults. Always use to_char() with an explicit date mask:

ops$tkyte%ORA11GR2> select to_char(sysdate,'dd Month yyyy') from dual;

TO_CHAR(SYSDATE,'
-----------------
17 November  2011

A date format is just a format. All oracle dates are stored in 7 bytes of binary data (century, year, month, day, hour, minute, second) and when we convert them to a string for us humans to view - we use the date format as a template. You can format the date however you like upon retrieval - it is just printing a date into a string using that format.

A format is NOT part of a date, a date is just 7 bytes of binary data.

2) When the DATE value we insert into database is not in 'database default format'
- we must use TO_DATE

when you insert a STRING into the database and the target field is a DATE - you must ALWAYS use to-date - always.

Sometimes you use it implicitly - as you did above. there we implicitly wrapped a to-date call around your string and used the default date format to convert it.

If your date doesn't conform to your sessions default date mask, you have to do this explicitly, using to_date right there in the code.

however, you used TO_DATE both times, every time, all of the times you insert a string into a date - we have to convert it. To_date does that.

I will recommend (strongly, vigorously, excitedly) that you ALWAYS USE TO_DATE, ALWAYS.

as you have already seen, the conversion is trying to be very "use friendly" and can be very confusing.

Furthermore, if you are in the US, your application might use "mm/dd/yyyy" as the default mask and when you go to Europe with your code, it might start using "dd/mm/yyyy" - what then??

bug - big bug.

BE EXPLICIT. when you have a string to put into a date, convert the string into a date using an explicit format.

3) dead on, absolutely correct - they are just formatting/conversion routines, they do not affect how the date is stored.

A reader, November 18, 2011 - 5:33 am UTC

Many thanks Tom for clarifying my doubts

Just see below in my case- I was wondering - the session_parameter=database_parameter but the SYSDATE was shown in different format - why is it so?

select * from nls_database_parameters where parameter = 'NLS_DATE_FORMAT';
DD-MON-RR

select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
DD-MON-RR

select sysdate from dual;
18/11/2011 11:28:03

November 21, 2011 - 1:13 pm UTC

see below, apparently it is TOAD doing this very confusing thing to you.

A reader, November 18, 2011 - 6:17 am UTC

Hi Tom,

1.When you supply the date string in the session's (client's) date format in your code- then client will internally convert it to database default format using TO_DATE, here the client will use TO_DATE implicitly

2.If you don’t know client's date format (or) database default format - then we must use TO_DATE in the CODE.
that will be directly passed to the database by the client and the database can understand that .

Is this correct Tom ?

Thanks

November 21, 2011 - 1:14 pm UTC

1) correct, it will use an evil, really bad, implicit conversion - yes.

2) see #1, I'm of the opinion that you should always be explicitly converting and not relying on defaults. Since defaults can change.

To: "A reader" - Re: Toad date formatting

Kim Berg Hansen, November 18, 2011 - 6:58 am UTC

Hi, "A reader"

Toad selects a DATE datatype as a DATE and not as a string, so the Oracle implicit TO_CHAR conversion is not relevant to Toad. Toad shows DATE datatypes according to the format you decide in Toad options under Data Grids, where you can choose display format for DATE.

It is the decision of each tool how that tool will work with dates - Toad uses a proprietary display format, SQL*Plus uses the session NLS settings. Other tools may do otherwise.

It is most often dangerous to rely on the tools doing implicit conversion :-) Whenever you can, do it explicitly.

A reader, November 18, 2011 - 10:50 am UTC

Hi 

thanks for your reply :
"
SQL*Plus uses the session NLS settings. Other tools may do otherwise"

but below :

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from nls_database_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER-  VALUE
NLS_DATE_FORMAT        DD-MON-RR


SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER-  VALUE
NLS_DATE_FORMAT        DD-MON-RR


SQL> select sysdate from dual;

SYSDATE
---------
18-NOV-11


thanks

November 21, 2011 - 1:19 pm UTC

you just showed that sqlplus appears to be using the session NLS settings. I'm not sure what your point was? What were you trying to "but below" about?

A reader, November 18, 2011 - 10:52 am UTC

sorry -ignore the above post please -bit confused ,
thats clear now !!

A reader, November 21, 2011 - 2:46 pm UTC

let us say sqlplus - nls_session_date_format='dd-month-year'
and nls_database_date_format='dd-mon-rr'
Hi Tom,

insert into t values ('01-january-2011');
sqlplus will do implicit conversion INTERNALLY as below :

case1 :
--------
insert into values (to_date(01-jan-11','dd-mon-rr')-->Databse engine.

(OR)

case2:
-------

insert into values (to_date(01-january-2011','dd-month-year')-->Databse engine.

which of the above cases is correct ? I mean- which of the above conversion is done by sqlplus?

when we say '
insert into t values ('01/01/2011');
ORA-01843: not a valid month

why SqlPlus not able to do implicit conversion here ??

I understand completely that we MUST do it explicit but wanted to know why sqlplus was not able
to do implicit conversion above ?

November 21, 2011 - 5:57 pm UTC

sqlplus will do implicit conversion INTERNALLY as below :

No, the DATABASE will do the implicit conversion using dd-month-yyyy. SQL plus just lets the default implicit conversion happen.

initially the default session setting = database setting = dd-mon-rr, I changed it and then the insert works

ops$tkyte%ORA11GR2> create table t ( x date );

Table created.

ops$tkyte%ORA11GR2> insert into t values ( '01-jan-2011 12:34:56' );
insert into t values ( '01-jan-2011 12:34:56' )
                       *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


ops$tkyte%ORA11GR2> @bigdate
ops$tkyte%ORA11GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte%ORA11GR2> insert into t values ( '01-jan-2011 12:34:56' );

1 row created.

ops$tkyte%ORA11GR2> select * from t;

X
--------------------
01-jan-2011 12:34:56

why SqlPlus not able to do implicit conversion here ??

it is, you just supplied garbage data that doesn't satisfy your default mask.

ops$tkyte%ORA11GR2> alter session set nls_date_format = 'dd-month-yyyy';

Session altered.


ops$tkyte%ORA11GR2> select to_date( '01/01/2011' ) from dual;
select to_date( '01/01/2011' ) from dual
                *
ERROR at line 1:
ORA-01843: not a valid month


ops$tkyte%ORA11GR2> alter session set nls_date_format = 'dd-mm-yyyy';

Session altered.

ops$tkyte%ORA11GR2> select to_date( '01/01/2011' ) from dual;

TO_DATE('0
----------
01-01-2011

ops$tkyte%ORA11GR2>

I don't know why that one would be confusing to you - you said "I want dd-month spelled out-yyyy" and you gave it dd/mm/yyyy - that doesn't fit the format.

A reader, November 22, 2011 - 4:18 am UTC

Hi Tom,

Good Morning !!

Summary :

Into the Database (String to Date):
=========================
When we set the session’s default date format (nls_date_format)- this will OVERWRITE the database default date format . We MUST always use EXPLICIT conversion using TO_DATE when we insert STRING into a DATE field in our code
Otherwise the database has to do implicit conversion using the SESSION’S DEFAULT FORMAT . This may not work well always (when your code moves from one region to other region in the world where the date format of the session(client) changes) .Different tools use different formats –we can view the SQLPlus session’s default format in nls_sessions_parameters But for Toad – we can’t see it in nls_session_parameters it uses its own default format set in the TOAD GUI.

Out of the Database (Date to String):
==========================
When we ask for a value from DATE field – the Database uses TO_CHAR internally and uses SESSION’S DEFAULT FORAMT to display the date .In this case also it is recommended that – we use EXPLICITLY -TO_CHAR in our Query for a nice format you wish .

From DATE filed to other DATE field:
==============================
Simply use TO_DATE on both side explicitly – that keeps the things clearer.
In the below case : both x and y are DATE fields.
Select x into y from t;
Select to_date(x,’dd-mon-yyyy’) into to_date(y,’dd-mon-yyyy’); from t;

Is this understanding correct ?

Many thanks Tom !!

November 22, 2011 - 8:35 am UTC

from date field to other date field

simply assign, do NOT use any functions whatsoever.

If you use to_date(), you'll do two implicit conversions.

ops$tkyte%ORA11GR2> select
  2  to_char(      sysdate,                   'dd-mon-yyyy hh24:mi:ss' ),
  3  to_char(      to_date(sysdate),          'dd-mon-yyyy hh24:mi:ss' ),
  4  to_char(      to_date(to_char(sysdate)), 'dd-mon-yyyy hh24:mi:ss' )
  5    from dual
  6  /

TO_CHAR(SYSDATE,'DD- TO_CHAR(TO_DATE(SYSD TO_CHAR(TO_DATE(TO_C
-------------------- -------------------- --------------------
22-nov-2011 09:33:31 22-nov-2011 00:00:00 22-nov-2011 00:00:00

sysdate by itself - perfect.

to_date(sysdate) is the SAME as to_date( to_char( sysdate) ) since to_date takes a STRING. By default, you'll lose the time component .

Also,

Select to_date(x,’dd-mon-yyyy’) into to_date(y,’dd-mon-yyyy’); from t;

doesn't make any sense. You cannot use a function on a lvalue (something you assign to). The to_date( y.... ) would never work.

Strange date dump

Yury, December 26, 2011 - 7:59 am UTC

Hi Tom!
From your book I know that 1 is added to hour, minute and second value in internal representaion I can see with dump.
But, consider the following:

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> column d format a50
SQL> select job_start, dump(job_start, 10) d from gr_dms_meta.dm_job where job_id = 470560;

JOB_START           D
------------------- --------------------------------------------------
2011-10-10 01:00:00 Typ=12 Len=7: 120,111,10,10,0,1,1

1) Why I see 0,1,1 as representation of 01:00:00?
2) Maybe my data is corrupted?
3) What should I check and do?

December 26, 2011 - 10:47 am UTC

ops$tkyte%ORA11GR2> create table t ( x date );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( to_date( '2011-10-10 01:00:00', 'yyyy-mm-dd hh24:mi:ss' ) );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select dump(x,10) from t;

DUMP(X,10)
-------------------------------------------------------------------------------
Typ=12 Len=7: 120,111,10,10,2,1,1

Yes, it would appear it is. How did this data get in there in the first place - what inserts/updates - what is it written in - what approach does it use to get it in there.

for example:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2078184919103

ORA-01830 with NVL and DECODE

Kuldeep, February 21, 2012 - 5:05 am UTC

Hi Tom,

I have a string which can contain 2 informations i.e. date and count. e.g. 29-02-12-4 has date 29-02-12 and count 4. If count information is missing (when only 2 "-" then this is only date information.

I wrote below code and getting ORA-01830 when using NVL. I tried this with DECODE and it is working when passing complete string.

------------------------
WITH t AS (SELECT '21-02-12-4' c1 FROM dual)
SELECT NVL(TO_DATE(SUBSTR(c1,1,INSTR(c1,'-',1,3)-1),'DD-MM-RRRR'),TO_DATE(c1,'DD-MM-RRRR')) idate
FROM t

DECLARE
-- v_string VARCHAR2(20):='21-02-12'; -- Working
v_string VARCHAR2(20):='21-02-12-4'; -- ORA-01830
v_date DATE;
BEGIN
SELECT NVL(TO_DATE(SUBSTR(v_string,1,INSTR(v_string,'-',1,3)-1),'DD-MM-RRRR'),TO_DATE(v_string,'DD-MM-RRRR'))
INTO v_date
FROM dual;
--
DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date,'dd-mon-yyyy'));
END;
/

WITH t AS (SELECT '21-02-12-4' c1 FROM dual)
SELECT DECODE(TO_DATE(SUBSTR(c1,1,INSTR(c1,'-',1,3)-1),'DD-MM-RRRR'),NULL,TO_DATE(c1,'DD-MM-RRRR'),TO_DATE(SUBSTR(c1,1,INSTR(c1,'-',1,3)-1),'DD-MM-RRRR')) idate
FROM t

------------------------

Is there any difference in parsing of these 2 functions like all arguments of NVL will be parsed while in DECODE only arguments on the basis of condition will be parsed?

Thanks and regards,

February 21, 2012 - 7:24 pm UTC

since the date is always there
and since the date is always 8 characters

why not just

select to_date( substr( str, 1, 8 ), 'dd-mm-rr' ) from t

?? why even bother with logic to look for the third dash, just take the first eight and convert.

NVL and Decode

Kuldeep, February 22, 2012 - 7:31 am UTC

Tom,

Because data information could be in DD-MM-YY or in DD-MM-YYYY format. So we are using this 3rd '-' logic instad of fix position.

Basically my question was about different behaviou of NVL and DECODE functions. When using NVL it is evaluating ALL arguments while running the query but when using DECODE it is evaluating only those argument which are mendatory OR meed decode condition.

Demonstration:
-------------------
-- ORA-01722, evaluating 2nd argument even when not needed because 1st argument is not null
WITH t AS (SELECT '1' c1, 'a' c2 FROM dual)
SELECT NVL(TO_NUMBER(c1),TO_NUMBER(c2)) val
FROM t

-- OK, not evaluating 4th argument because as per values of first and 2nd this will not be returned
WITH t AS (SELECT '1' c1, 'a' c2 FROM dual)
SELECT DECODE (TO_NUMBER(c1),1,-999,TO_NUMBER(c2)) val
FROM t

-------------------

Thanks and regards,

February 23, 2012 - 7:18 pm UTC

that is the way NVL works - some functions are truly functions - they evaluate the parameter list, pass it, and get the results.

Others use more of an inline feature (decode for example) - which do a lazy evaluation.

it is just the way they work.

I still don't see why you need to look for the '-', your example code above didn't need to.

You might prefer to use CASE over either of them - CASE does a lazy evaluation and leads to more readable code (assuming you format things readable that is) in general.

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Trigger after logon

RE: Trigger after logon

Bytes for Date ...

more about dates

great explanation

date query

when date not store in table

strange dates

oracle.sql.TIMESTAMP does not properly convert BC dates to java.sql.Timestamp

00-000-0000 date

Re: 00-000-0000 date

00-000-0000 date

00-000-0000 date

Invalid date values

Using dbms_stats.convert_raw_value ...

Checking of valid dates

about Date datatype

Data Format

Query to get bad data in a date field

Insert date

Insert date

To: "A reader" - Re: Toad date formatting

Strange date dump

ORA-01830 with NVL and DECODE

NVL and Decode

More to Explore

Live SQL

Dev Gym