so just to be absolutely clear,
we are using timestamp in the mapping
for example (cut right from our mapping files)
<field name="activationDate" type="date">
<sql name="activation_date" type="timestamp"/>
</field>
we are not converting the date to a string.
what are you specifying as the column type in oracle for a datetime?
btw:
1. java sql date type does have a time component.
(http://java.sun.com/j2se/1.3/docs/api/java/sql/Date.html)
2. SQL92 or SQL3 date type is calendar date without time of day
3. Oracle (NON standard sql) date type does have time of day. Timestamps do
not exist in Oracle.
The jdbc driver will, upon getting a timestamp bound to a prepared
statement, make it into a date, and use that in the SQL.
Oracle has a default date format that is good to one day of precision. If
you try to compare a date (or timestamp) that has a time component, it will
fail.
if you change the default oracle date format to include the time component
then your comparisons will work.
Oracle is only date-precise to one second, it does not manage milliseconds.
these are the facts as I know them.
read http://www.oreview.com/9811feat2.html and
http://www.arsdigita.com/books/sql/dates.html
for more information.
Please let me know if I have misunderstood anything, although I believe this
is pretty clear: Oracle doesn't do dates correctly, and you need to "make
them work" either by using to_date or setting the date format to get
date/time precision.
m
-----Original Message-----
From: Tim Fox [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 04, 2001 10:02 AM
To: [EMAIL PROTECTED]
Subject: Re: [castor-dev] oracle date whackiness
I'm using oracle (8.1.7) and using castor fine with timestamps.
In your query bind using a java.util.Date eg.
OQLQuery q = db.getOQLQuery("select ...");
q.bind(new java.util.Date());
etc.
In your mapping.xml specify the field of interest as a timestamp type:
<field name="myObjectField" type="date" required="true">
<sql name="mydbcolumn" type="timestamp"/>
</field>
That should work fine (does for me anyway).
You shouldn't need to mess around with timestamps in your code, and you
certainly shouldn't be converting your dates to strings in queries before
executing them - that's just downright dirty.
This looks to me like a classic confusion between the SQL type "date" (which
doesn't contain a time component) and the Java type "date" (which does).
HTH
> -----Original Message-----
> From: Matthew Baird [mailto:[EMAIL PROTECTED]]
> Sent: 04 October 2001 17:31
> To: [EMAIL PROTECTED]
> Subject: Re: [castor-dev] oracle date whackiness
>
>
> we definitely ARE doing the following, take the date, get the
> time (as long)
> and make a java.sql.timestamp and bind that to the castor token
>
> new java.sql.Timestamp(auditDate.getTime())
>
> I'm not sure what is meant by castor "may" convert it, but I do know this:
> oracle needs dates in the format that you have set as the default nls
> format, otherwise you MUST call to_date(date,format) on the date
> before you
> do any comparisons.
>
> Perhaps you are saying that when I send in a timestamp, castor and/or the
> jdbc driver will do a comparison on a timestamp instead of a date?
>
> so
>
> WHERE somedate > someotherdate
>
> will become
>
> WHERE sometimestamp > someothertimestamp
>
> and thus work correctly?
>
> Somehow I don't think so. Please explain your reasoning.
>
> m
>
>
> -----Original Message-----
> From: Tim Fox [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 04, 2001 2:02 AM
> To: [EMAIL PROTECTED]
> Subject: Re: [castor-dev] oracle date whackiness
>
>
> I agree - this doesn't look an Oracle problem to me.
> Much more likely you're using the wrong type in your code -
> use timestamp rather than date.
>
>
>
> > -----Original Message-----
> > From: Ilia Iourovitski [mailto:[EMAIL PROTECTED]]
> > Sent: 03 August 2001 23:51
> > To: [EMAIL PROTECTED]
> > Subject: Re: [castor-dev] oracle date whackiness
> >
> >
> > Another solution is to try to use java.sql.TIMESTAMP.
> > If you specify sql type of the field as timestamp castor "may"
> convert it.
> >
> > Ilia
> >
> > -----Original Message-----
> > From: Ryan Campbell [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, October 03, 2001 3:19 PM
> > To: [EMAIL PROTECTED]
> > Subject: Re: [castor-dev] oracle date whackiness
> >
> >
> > Well, your *default* date format is hard-coded at the oracle
> level whether
> > you like it or not. I'm just saying that you can change that default.
> >
> > Ryan
> >
> > -----Original Message-----
> > From: Dmitri Colebatch [mailto:[EMAIL PROTECTED]]
> >
> > Are we suggesting here that the date format can be hard coded at the
> > oracle level and that'll be ok? I would've thought that if you
> do date >
> > $1 and set the param as a date type it would be ok, but I'm pretty
> > green so this may not be the case with castor... its the way it
> would work
> > with a prepared statement though, which is what we're really after yes?
> >
> > cheers
> > dim
> >
> > On Wed, 3 Oct 2001, Ryan Campbell wrote:
> >
> > > How about a solution that doesn't involve castor? If you
> don't have any
> > > other apps running on that oracle instance, you could just set the
> > > nls_date_format (in your $ORACLE_HOME/dbs/initSID.ora file) to your
> > required
> > > format. Of course if other applications rely on the date being
> > in the old
> > > oracle default, you can't do this.
> > >
> > > Ryan
> > >
> > > -----Original Message-----
> > > From: Matthew Baird [mailto:[EMAIL PROTECTED]]
> > > Sent: Wednesday, October 03, 2001 1:32 PM
> > > To: [EMAIL PROTECTED]
> > > Subject: [castor-dev] oracle date whackiness
> > >
> > >
> > > hey Guys,
> > >
> > > I'm trying to use some date comparisons in both oracle and sql server,
> > very
> > > vanilla WHERE DATE > SOMEDATE kinda stuff. I need precision
> down to the
> > > second. in SQL Server it works great. In oracle the default
> is DAY level
> > of
> > > precision, unless I do the following
> > >
> > > where DATE > to_date('1999-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
> > >
> > > is there anyway to tell Castor to do this? Should it do this
> > automatically
> > > for oracle?
> > >
> > > thanks,
> > > Matthew
> > >
> > > -----------------------------------------------------------
> > > If you wish to unsubscribe from this mailing, send mail to
> > > [EMAIL PROTECTED] with a subject of:
> > > unsubscribe castor-dev
> > >
> > > -----------------------------------------------------------
> > > If you wish to unsubscribe from this mailing, send mail to
> > > [EMAIL PROTECTED] with a subject of:
> > > unsubscribe castor-dev
> > >
> > >
> >
> > -----------------------------------------------------------
> > If you wish to unsubscribe from this mailing, send mail to
> > [EMAIL PROTECTED] with a subject of:
> > unsubscribe castor-dev
> >
> > -----------------------------------------------------------
> > If you wish to unsubscribe from this mailing, send mail to
> > [EMAIL PROTECTED] with a subject of:
> > unsubscribe castor-dev
> >
> > -----------------------------------------------------------
> > If you wish to unsubscribe from this mailing, send mail to
> > [EMAIL PROTECTED] with a subject of:
> > unsubscribe castor-dev
> >
>
> -----------------------------------------------------------
> If you wish to unsubscribe from this mailing, send mail to
> [EMAIL PROTECTED] with a subject of:
> unsubscribe castor-dev
>
> -----------------------------------------------------------
> If you wish to unsubscribe from this mailing, send mail to
> [EMAIL PROTECTED] with a subject of:
> unsubscribe castor-dev
>
-----------------------------------------------------------
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-dev
-----------------------------------------------------------
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-dev
