Thanks for the question, Nag.

Asked: August 25, 2002 - 11:05 am UTC

Last updated: July 21, 2004 - 8:36 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

SQL> DROP TABLE T;

Table dropped.

SQL> create table t( x date);

Table created.

SQL> insert into t values('01-JAN-2003');

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(x,'mm/dd/yyyy') from t;

TO_CHAR(X,
----------
01/01/2003

SQL> select to_date(to_char(x,'mm/dd/yyyy'),'mm/dd/yyyy') from t;

TO_DATE(T
---------
01-JAN-03

*******I WANT TO SELECT THE DATE VALUE FROM THE DATE COLUMN IN THE 'MM/DD/YYYY' FORMAT. BUT I'M NOT BEING ABLE TO DO SO. I'M CONVERTING IT FIRST INTO CHAR , AND THEN CONVERTING IT INTO DATE ONCE AGAIN INTO A DATE WITH 'mm/dd/yyyy' FORMAT ,BUT IT IS NOT WORKING .. WHY?

--NEITHER DOES WORK THE BELOW

SQL> select to_date(x,'mm/dd/yyyy') from t;
select to_date(x,'mm/dd/yyyy') from t
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

NOW i'm trying to convert a date stored in a varchar2 column as a date and I'm having the problem again..

SQL> DESC T;
Name Null? Type
----------------------------------------- -------- ----------------------------
X VARCHAR2(10)

SQL> SELECT * FROM T;

X
----------
O1-JAN-02

SQL> SELECT to_date(x,'mm/dd/yyyy') FROM T ;
SELECT to_date(x,'mm/dd/yyyy') FROM T
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

and Tom said...

You are misunderstanding.

A date is stored in an internal 7 byte format that the machine likes to use (bits and bytes).

For us poor humans, who cannot read the bits and bytes -- we have date formats. The formats are EDITS applied to the bits and bytes that convert them from the internal format (a DATE) to a string so we can read them.

The dates are not STORED using that format -- the format is used only for DISPLAY when you select it out. All dates are stored in a 7 byte machine format (and in 9i, a timestamp might take 11 bytes for timezones and fractional seconds).

So, ALL YOU NEED TO DO (after you stop shouting) is

select to_char( x, 'mm/dd/yyyy' ) from t;

When you insert, simply "insert into t values ( to_date( SOME_STRING, 'mm/dd/yyyy' ) )"

You can change the default date format using alter session set nls_date_format='mm/dd/yyyy'; but I would strongly DISCOURAGE that. You should use explicit formats upon insert/update and select -- that way, you know exactly what you'll get and in case someone else changes the session settings, you won't be affected AT ALL.

Rating

(13 ratings)

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

Comments

Sorry for shouting .. but

Nag, August 25, 2002 - 7:32 pm UTC

I have no control over how users insert.. what I want to see is how I can convert

select to_char( x, 'mm/dd/yyyy' ) from t; into a date with the date format mm/dd/yyyy

is that possible ?

August 25, 2002 - 9:21 pm UTC

I told you how. alter session. See also
</code> http://asktom.oracle.com/Misc/NLSDateFormat.html <code>

there you go -- and guess what, you DO in fact have control over how they insert -- as their inserts will NOT work in any arbitrary format (and lack of access is the ultimate in control). Hence, they will either need to alter session (bad idea) OR they will need to say what they mean (always a good idea)

Clarification

Nasser, February 08, 2003 - 4:26 pm UTC

Tom

So if the Nls_date_format is set to 'dd-mon-yy' , not matter how you enter the date it will insert it in the same format i.e. dd-mon-yy..

Is that what you are tryig to say.. I have tested this as below..

SQL> create table t( x date);

Table created.

SQL> insert into t values(sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

X
---------
08-FEB-03

SQL> insert into t values(to_date('02/08/2003', 'mm/dd/yyyy') );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

X
---------
08-FEB-03
08-FEB-03

SQL> insert into t values(to_date('2003/02/07','yyyy/mm/dd'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

X
---------
08-FEB-03
08-FEB-03
07-FEB-03


So now if I want to start storing the date in my datbase in 'yyyy/mm/dd' format then I should set my nls_date format to 'yyyy/mm/dd.

February 08, 2003 - 4:43 pm UTC

No, not at all:

<quote>
A date is stored in an internal 7 byte format that the machine likes to use
(bits and bytes).

For us poor humans, who cannot read the bits and bytes -- we have date formats.
The formats are EDITS applied to the bits and bytes that convert them from the
internal format (a DATE) to a string so we can read them.
</quote from above...>

the format string is just an edit, just an edit -- a format, nothing more, nothing less.

the date always has century, year, month, day, hour, minute, second -- regardless of the format used to insert it or retrieve it.

Got you...

Nasser, February 08, 2003 - 5:02 pm UTC

SQL> alter session set nls_date_format='yyyy-mm-dd' ;

Session altered.

SQL> select * from t;

X
----------
2003-02-08
2003-02-08
2003-02-07

insert into t values(to_date('02/08/2003', 'mm/dd/yyyy') );

SQL> select * from t;

X
----------
2003-02-08
2003-02-08
2003-02-07
2003-02-08


How can I know the nls_date_format of our database?

7 byte format

Nasser, February 08, 2003 - 5:10 pm UTC

Tom

SQL> select length('dd-mon-yyyy') from dual;

LENGTH('DD-MON-YYYY')
---------------------
                   11

A dates internal format should take 11 bytes. How come according to you it is only 7 bytes...

What is the format which enables storage of date in  7 bytes, please give insight into it...

7 bytes

Tony Andrews, February 08, 2003 - 6:11 pm UTC

Nasser, that's really funny!  But you could have gone further:

SQL> select to_char( sysdate,'DAY DD MONTH YYYY HH:MM:SSAM') from dual
  2  /

TO_CHAR(SYSDATE,'DAYDDMONTHYYYYHH:MM:S
--------------------------------------
SATURDAY  08 FEBRUARY  2003 11:02:27PM

SQL> select length(to_char( sysdate,'DAY DD MONTH YYYY HH:MM:SSAM')) from dual
  2  /

LENGTH(TO_CHAR(SYSDATE,'DAYDDMONTHYYYYHH:MM:SSAM'))
---------------------------------------------------
                                                 38

And it STILL fits into 7 bytes!!!  I guess that's the difference between INTERNAL formats and EXTERNAL formats for you!

Dear Tony

Nasser, February 08, 2003 - 7:21 pm UTC

My question in brief was...

What is the internal format in which the date is stored? and I was just giving an example.

Full Format

LakshmiNarasimhan, February 09, 2003 - 1:53 am UTC

I believe Oracle will store every portion of date ,

The Year (Full year)
Month
Date
Time (AM / PM indicator or 24 hours format)

Am I rigt Tom?

Regards

Date format

Tony Andrews, February 09, 2003 - 5:43 am UTC

Nasser,

Sorry my previous answer was a little "tongue in cheek"!

Oracle DATE columns are stored internally as the number of seconds since some date way in the past. We have 7 bytes to store it in - how big is that?
1 byte = 8 bits. This can store numeric values up to (2**8)-1 = 255
7 bytes = 56 bits. This can store numeric values up to (2**56)-1 = 7.2058E+16, i.e. 72,058,000,000,000,000

That is the number of different DATE values, in seconds, there can be. How many years is that?

7.2058E+16 / 60 / 60 / 24 / 365 = 2,284,931,318

i.e. 2 billion years.

So with a 7-byte DATE column you could in theory record any date and time (to the second) from around 1 billion BC to around 1 billion AD.

I'm not saying that's EXACTLY how Oracle dates work, but it is similar, and shows how they could easily accomodate all the dates you'll ever need in 7 bytes or less.

February 09, 2003 - 3:15 pm UTC

see
</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c10datyp.htm#796


it is not the number of seconds since some epoch - but rather the 

1 century
2 year
3 month
4 day 
5 hour
6 minute
7 second

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76975/oci03typ.htm#421890 <code>

has the exact layout/details.

Helena Markova, February 10, 2003 - 4:58 am UTC

Get ready all you consultants!!!

John, February 10, 2003 - 12:52 pm UTC

The year 2000 problem WILL happen again and we'll call it the year 4712 problem.

Date format chaning query results

A reader, September 11, 2003 - 4:54 pm UTC

The date format 'MM RRRR' is returning wrong results where as 'RRRRMM' returning the correct
data. Can you please explain as to why this is happening. Is there a more efficient way of doing the date comparision for the given case.

SQL> DESC TEST;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 BEGIN_DT                                           DATE
 END_DT                                             DATE
 VOL                                                NUMBER

SQL> SELECT * FROM TEST;

        ID BEGIN_DT  END_DT           VOL
---------- --------- --------- ----------
         1 26-FEB-03 31-DEC-03
         2 01-JAN-03 31-DEC-07        100
         3 01-JAN-03 31-DEC-03          3
         4 01-JAN-04 31-DEC-08        400
         5 01-JAN-03 31-DEC-04         80
         6 01-JAN-05 31-DEC-14       1000
         7 01-MAY-03 30-SEP-03        100

7 rows selected.

SQL>   select  *
  2            from TEST 
  3            where TO_CHAR(BEGIN_dT,'MM RRRR') <=  TO_CHAR(SYSDATE, 'MM RRRR')
  4               and  TO_CHAR(end_dT,'MM RRRR') >= TO_CHAR(SYSDATE, 'MM RRRR')   
  5  /

        ID BEGIN_DT  END_DT           VOL
---------- --------- --------- ----------
         1 26-FEB-03 31-DEC-03
         2 01-JAN-03 31-DEC-07        100
         3 01-JAN-03 31-DEC-03          3
         4 01-JAN-04 31-DEC-08        400
         5 01-JAN-03 31-DEC-04         80
         6 01-JAN-05 31-DEC-14       1000
         7 01-MAY-03 30-SEP-03        100

7 rows selected.

SQL>  select  *
  2            from TEST 
  3            where TO_CHAR(BEGIN_dT,'RRRRMM') <=  TO_CHAR(SYSDATE, 'RRRRMM')
  4               and  TO_CHAR(end_dT,'RRRRMM') >= TO_CHAR(SYSDATE, 'RRRRMM')   
  5  /

        ID BEGIN_DT  END_DT           VOL
---------- --------- --------- ----------
         1 26-FEB-03 31-DEC-03
         2 01-JAN-03 31-DEC-07        100
         3 01-JAN-03 31-DEC-03          3
         5 01-JAN-03 31-DEC-04         80
         7 01-MAY-03 30-SEP-03        100

September 11, 2003 - 7:54 pm UTC

it is giving you 110% what you asked for.

think about it.

think "strings"

you do not have dates -- you have two strings.

tell me, which is "bigger"

12 xxxx
01 xxxx

??? hmmm, now what about

12 1902
01 2001

which is bigger? (same answer, they are STRINGS not dates!!!!!)

you have nice DATES!!! why -- why -- why would you convert them into strings?!?!

where begin_dt < add_months(trunc(sysdate,'mm'),1)
and end_dt >= trunc(sysdate,'mm')

that is what you want.

give me everything where the begin_date happens this month and before AND end_date is in this month or later.

Strange behaviour while converting to date !!!

Shailendra, July 20, 2004 - 11:44 am UTC

Hi Tom,

We have observed a very strange behaviour while converting text to Date in a view and then selecting from it. Hope you would be able to throw some light on it as we are clueless.
Following is a Copy/paste from the SQL Plus window. The database version is 8.1.7

SQL*Plus: Release 8.0.5.0.0 - Production on Tue Jul 20 16:29:2 2004

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


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

SQL> variable MON_TXT varchar2(8);
SQL> exec :MON_TXT := 'FEB-2004';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM 
  2  ( 
  3    SELECT 
  4      TO_DATE(MONTH,'MON-YYYY')      COL_TIME
  5    FROM 
  6      ( 
  7      SELECT 'JAN-2004' MONTH FROM DUAL 
  8      UNION ALL
  9      SELECT 'FEB-2004' MONTH  FROM DUAL 
 10      ) 
 11  )
 12  WHERE COL_TIME = TO_DATE(:MON_TXT, 'MON-YYYY');

no rows selected

SQL> exec :MON_TXT := 'JAN-2004';

PL/SQL procedure successfully completed.

SQL> /

COL_TIME
---------
01-FEB-04
01-JAN-04

SQL> 

The section where you see the text
----------------
  7      SELECT 'JAN-2004' MONTH FROM DUAL 
  8      UNION ALL
  9      SELECT 'FEB-2004' MONTH  FROM DUAL 
----------------
I have put for simulation purposes but the actual business data is obtained via csv files which are loaded into db using SQL Loader. The business data is very similar to the simulated data the only difference being few extra columns present in business data like fact and dimensions which are irrelevant for current problem.

Thanks for you help,
Shailendra

July 20, 2004 - 8:29 pm UTC

you are not clueless, it is definitely doing the wrong thing (does not reproduce in 9ir2 btw).

workaround appears to be:

12 WHERE COL_TIME = (select TO_DATE(:MON_TXT, 'MON-YYYY') from dual);

I reproduced in 817 your issue. Please contact support a file a bug with your very simple, clear test case.

Thanks very much

Shailendra, July 21, 2004 - 7:07 am UTC

Hi Tom,

Hmm interesting !
How does the "work around" work then? What change does it make to the query plan(as the queries are same semantically)?

I did some investigation on query plans and found that the introduction of select from dual forces a join/filter on the resultset , instead of individual filters which were happening before. But not sure hence the question. Below are the explain plan of the two statements:

SQL> variable MON_TXT varchar2(8);
SQL> exec :MON_TXT := 'JAN-2004';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM 
  2    ( 
  3      SELECT 
  4        TO_DATE(MONTH,'MON-YYYY')      COL_TIME
  5      FROM 
  6        ( 
  7        SELECT 'JAN-2004' MONTH FROM DUAL 
  8        UNION ALL
  9        SELECT 'FEB-2004' MONTH  FROM DUAL 
 10        ) 
 11    )
 12  WHERE COL_TIME = TO_DATE(:MON_TXT, 'MON-YYYY');

COL_TIME
---------
01-JAN-04
01-FEB-04


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW
   2    1     UNION-ALL
   3    2       FILTER
   4    3         TABLE ACCESS (FULL) OF 'DUAL'
   5    2       FILTER
   6    5         TABLE ACCESS (FULL) OF 'DUAL'



SQL> SELECT * FROM 
  2    ( 
  3      SELECT 
  4        TO_DATE(MONTH,'MON-YYYY')      COL_TIME
  5      FROM 
  6        ( 
  7        SELECT 'JAN-2004' MONTH FROM DUAL 
  8        UNION ALL
  9        SELECT 'FEB-2004' MONTH  FROM DUAL 
 10        ) 
 11    )
 12  WHERE COL_TIME = (SELECT TO_DATE(:MON_TXT, 'MON-YYYY') FROM DUAL);

COL_TIME
---------
01-JAN-04


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     VIEW
   3    2       UNION-ALL
   4    3         TABLE ACCESS (FULL) OF 'DUAL'
   5    3         TABLE ACCESS (FULL) OF 'DUAL'
   6    1     TABLE ACCESS (FULL) OF 'DUAL'


Thanks,
Shailendra

July 21, 2004 - 8:36 am UTC

it is a bug -- please file one with support. You can use the workaround as a "workaround", for unless this was already bugged (support is best to do the research on this), it take some amount of time to fix it (and you probably don't want to wait). Also, they will fix it in 8174 -- not 8173.

(workaround works by preventing the pushing of the predicate)

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

Sorry for shouting .. but

Clarification

Got you...

7 byte format

7 bytes

Dear Tony

Full Format

Date format

Get ready all you consultants!!!

Date format chaning query results

Strange behaviour while converting to date !!!

Thanks very much

More to Explore

Live SQL

Dev Gym

Select a date value in a date column in a different format

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Sorry for shouting .. but

Clarification

Got you...

7 byte format

7 bytes

Dear Tony

Full Format

Date format

Get ready all you consultants!!!

Date format chaning query results

Strange behaviour while converting to date !!!

Thanks very much

More to Explore

Live SQL

Dev Gym