Home » SQL & PL/SQL » SQL & PL/SQL » what is the size of date datatype?
what is the size of date datatype? [message #267369] Thu, 13 September 2007 02:09 Go to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Hi All,
can any one tell me the size of date datatype?
Thanks and regards
Sunil
Re: what is the size of date datatype? [message #267372 is a reply to message #267369] Thu, 13 September 2007 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
7 bytes.

Regards
Michel
Re: what is the size of date datatype? [message #267378 is a reply to message #267369] Thu, 13 September 2007 02:26 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
select vsize(sysdate) from dual


And it return 8

Do you (Michel) please clarify about it?
Re: what is the size of date datatype? [message #267382 is a reply to message #267378] Thu, 13 September 2007 02:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> select vsize(hiredate) from emp;

VSIZE(HIREDATE)
---------------
              7


There was a post about 1 week ago regarding the difference in size between sysdate and a date column.
Remember sysdate is NOT a date column stored in a table.
Re: what is the size of date datatype? [message #267383 is a reply to message #267369] Thu, 13 September 2007 02:36 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
select vsize(to_date('01-jan-2007')) from dual


This also return 8.
Re: what is the size of date datatype? [message #267385 is a reply to message #267383] Thu, 13 September 2007 02:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And that is not a column-size in a table either.
Like I said, there was a discussion about this difference a while ago. If you had taken the trouble of searching for it, you would have found it, like I did.
Re: what is the size of date datatype? [message #267386 is a reply to message #267369] Thu, 13 September 2007 02:58 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What is this then?

select distinct data_length,default_length from dba_tab_columns where data_type='DATE' SQL> ;

DATA_LENGTH DEFAULT_LENGTH
----------- --------------
          7              4
          7              8
          7             18
          7
          8
          7              7
          7              5
          7              9

8 rows selected.

Re: what is the size of date datatype? [message #267396 is a reply to message #267386] Thu, 13 September 2007 03:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you do a bit of digging, you;ll find that the entries with a length of 8 are columns in system views. These columns are all TO_DATE functions on other values, so I they aren't DATE columns in the database either.
Re: what is the size of date datatype? [message #267549 is a reply to message #267383] Thu, 13 September 2007 12:36 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
muzahidul islam wrote on Thu, 13 September 2007 03:36
select vsize(to_date('01-jan-2007')) from dual


This also return 8.


You still haven't learned about DATEs yet? What is so hard to understand? Your query is invalid. Please read up on the TO_DATE function.
FOO SCOTT>select vsize(to_date('01-jan-2007')) from dual;
select vsize(to_date('01-jan-2007')) from dual
                     *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected



Re: what is the size of date datatype? [message #267679 is a reply to message #267369] Fri, 14 September 2007 04:08 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
joy_division -

Forgive me for being a bit thick - but I went away and read about the to_date function and most sites I found just mention the standard functionality of to_date(string,format).

Do you have a link to explain why your go at that to_date didn't work - or a search term I can use?

Thanks
Re: what is the size of date datatype? [message #267681 is a reply to message #267679] Fri, 14 September 2007 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just because he didn't give a format, so default format is used and default format did not match input date string.

In summary: ALWAYS give a format model.

Regards
Michel

[Updated on: Fri, 14 September 2007 04:16]

Report message to a moderator

Re: what is the size of date datatype? [message #267682 is a reply to message #267369] Fri, 14 September 2007 04:18 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Doh of course Embarassed

Thanks for that.
Re: what is the size of date datatype? [message #267685 is a reply to message #267378] Fri, 14 September 2007 04:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
muzahidul islam wrote on Thu, 13 September 2007 03:36
Quote:
select vsize(to_date('01-jan-2007')) from dual


This also return 8.


Actually, I think you're wrong about that and about the size of sysdate:
  1* select vsize(to_date('01-jan-2007','dd-mon-yyyy')) from dual
SQL> /

VSIZE(TO_DATE('01-JAN-2007','DD-MON-YYYY'))
-------------------------------------------
                                          7

SQL> ed
Wrote file afiedt.buf

  1* select vsize(sysdate) from dual
SQL> /

VSIZE(SYSDATE)
--------------
             7
Re: what is the size of date datatype? [message #267691 is a reply to message #267685] Fri, 14 September 2007 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This depends on version.
Oracle fixed that in the latest ones.

Regards
Michel
Re: what is the size of date datatype? [message #267696 is a reply to message #267691] Fri, 14 September 2007 05:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ahh. Thatr explains it.

It interesting that I still get the length 8 fields appearing in the list from Dba_Tab_Columns though.
Re: what is the size of date datatype? [message #267712 is a reply to message #267696] Fri, 14 September 2007 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think some parts were fixed (like vsize) and others not.
Actually Oracle returns a 8 bytes buffer with last byte to 0.

Regards
Michel
Re: what is the size of date datatype? [message #267782 is a reply to message #267549] Fri, 14 September 2007 21:52 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
joy_division wrote on Thu, 13 September 2007 12:36
muzahidul islam wrote on Thu, 13 September 2007 03:36
select vsize(to_date('01-jan-2007')) from dual

This also return 8.


You still haven't learned about DATEs yet? What is so hard to understand? Your query is invalid. Please read up on the TO_DATE function.
FOO SCOTT>select vsize(to_date('01-jan-2007')) from dual;
select vsize(to_date('01-jan-2007')) from dual
                     *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected




But i get result by executing that query
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select to_date('01-jan-2007') from dual;

TO_DATE('
---------
01-JAN-07

SQL> select vsize(to_date('01-jan-2007')) from dual;

VSIZE(TO_DATE('01-JAN-2007'))
-----------------------------
                            8


If we follow the link
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions137a.htm
Then we can find the following sentence from that document.
Quote:

TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype.
The fmt is a date format specifying the format of char. If you omit fmt, then char must be in the default date format.


Re: what is the size of date datatype? [message #267785 is a reply to message #267782] Sat, 15 September 2007 00:21 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You certainly can ignore the advice of pretty much every single expert on this forum if you want to. You can also cross a motorway whilst blindfolded. Neither seems like a good idea to me. Picture the scene. You write all of your code, ignoring the simple and oft repeated expert advice to use to_date and the appropriate format model for the string. This amounts to several hundred packages, procedures, functions, statements embedded within your application code, text files holding useful scripts et al. For some reason, the business decides to change the default format of the system (say, to match up to the format of the company that has just bought you over) Now every single piece of code that you wrote that involved these strings (the ones that you seem to think are dates) no longer works. Have fun explaining to your new bosses why this is, when all you had to do was follow a simple piece of advice.
Re: what is the size of date datatype? [message #267786 is a reply to message #267369] Sat, 15 September 2007 00:28 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
pablolee,

mega dittos

Translation available upon request.
Re: what is the size of date datatype? [message #267787 is a reply to message #267786] Sat, 15 September 2007 00:34 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Well it's 6:30am here and I'm just sitting here resting my leg cos I bust it up playing football last night (translated to soccer for those across the pond Smile ) and I can't sleep so I figured I'd join the fray, whilst waiting for a new game of poker to start Smile
Re: what is the size of date datatype? [message #267789 is a reply to message #267369] Sat, 15 September 2007 00:42 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
It is late here for me after a VERY long week short on sleep.
Here in the USA we have a "talk radio host of Rush Limbaugh".
#1 in USA
His fans give "mega dittos" ( millions of that is what I would have said).
Simply put, it is a major compliment.

Re: what is the size of date datatype? [message #267790 is a reply to message #267789] Sat, 15 September 2007 00:45 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Well, thank you ana. I appreciate it. Now go and get yourself some sleep Smile You sound like you could do with it Thumbs Up
Re: what is the size of date datatype? [message #267792 is a reply to message #267790] Sat, 15 September 2007 00:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
These types of jewels should be stores somewhere central, so everyone should come across it sooner or later.
I bet you that once you read this, you will be bound to remember.
This explanation/warning falls in the same 'remembrance'-league as the 'pick the first from a bag of balls' Ana came up with. (and I borrowed when appropriate).
Re: what is the size of date datatype? [message #267794 is a reply to message #267792] Sat, 15 September 2007 00:57 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Wow, true accolades from 2 of the most prolific and knowledgeable posters on OraFaq. Much appreciated guys, thanks. (It's like Simon Cowell saying he likes your voice Very Happy Laughing)
Re: what is the size of date datatype? [message #267799 is a reply to message #267369] Sat, 15 September 2007 01:07 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>This explanation/warning falls in the same 'remembrance'-league as the 'pick the first from a bag of balls' Ana came up with. (and I borrowed when appropriate).
I did NOT come up with the bag of balls.
I honestly don't recall from who I got this,
but I would defer to Michel Cadot.

This has been a long week.
This has been a long year.
I am getting too old for this nonsense.
Re: what is the size of date datatype? [message #267806 is a reply to message #267799] Sat, 15 September 2007 01:25 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I prefer bag of apples and other kind of fruits. Cool

Regards
Michel
Previous Topic: Hard coding month and day to a year
Next Topic: How to take History for particular table? (merged)
Goto Forum:
  


Current Time: Sat Dec 03 16:08:08 CST 2016

Total time taken to generate the page: 0.09948 seconds