Re: DATE datatype(Is the Time in there too?)

Oracle stores in an internal format (11 bytes I think) - anyway it stores year(4 digit), month,day, hour,minute and second

the only reason that
"select * from table; returns dates 23-OCT-98 , 24-OCT-98, 25-OCT-98" it that 'dd-mon-yy' happens to be your default date-format. You can format it any way you want as : select to_char(saledate,'yyyy/mm/dd - hh24:mi:ss') for example

The reason that " select * from table where saledate = '24-OCT-98';" goes wrong is that Oracle translates '24-OCT-98' to a date with hour,minute and second set to zero. You can use truncate like select * from table where trunc(saledate) = '24-OCT-98' will give what you are after
Hope this helps
Kurt Laugesen

Rob Williamson wrote in message <3649B4E9.416FA48C_at_physics.umd.edu>...
>How is the date stored in the database?
>
>table has a date field ( saledate date )
>
>select * from table; returns dates 23-OCT-98 , 24-OCT-98, 25-OCT-98
>
>If you try select * from table where saledate = '24-OCT-98';
>
>I get no error but no rows returned ( I assume there is a time in the
>date field )
>
>if I use ( where saledate > '24-OCT-98' ) I will get all the Oct 24
>listings as
>well as the Oct 25 listings.
>
>If I try '24-OCT-98 12:00' I get an error saying I have exceeded the
>format range
>or somthing.
>
>I am still playing with it but don't like wasting so much time on such
>a trivial problem.
>
>Is there a way to see exactly what is in the table
>and if so Why does it return only the Date 24-OCT-98
>and not the date and time?
>
>Also if what I believe is true why does it allow me to insert just the
>date with no time
>is it adding system time or some other default value?
>
>We only do transactions durring the day so I assume the database
>designer knew that.
>If that matters.
>
>Thanks for anyones help in advance.
>
Received on Wed Nov 11 1998 - 11:01:11 CST