Wow, Ariel, you're amazing...!
Thanks for such a great reply! i'll check it out
once i get home this p.m.
Also, thanks for replying! i thought i was never
going to get an answer. It looks like the new version
of the db program will be coming out with better
Date/Time functions... sure hope so, this has really
been painful.
Have a great afternoon down there in beautiful ar!
:)
______________________________________________________
--- Ariel Constenla-Haile
<[EMAIL PROTECTED]> wrote:
> Hi Andreas
>
> Andreas Saeger escribió:
> > Extract the three numbers representing year, month
> and day, concatenate
> > them to ISO string "1999-12-31" and convert string
> to date.
> >
> > If this is the only way at all, it _seems_ to work
> well with years and
> > months. It fails with _some_ day-offsets when the
> resulting day is in
> > next year.
> >
> >> SELECT "Date",
> >> Convert( Year("Date") || '-' || Month("Date") ||
> '-' || Day("Date")+99, DATE ) AS "+3 years"
> >> FROM "Dates"
>
> Date calculation is one of the missing things in
> HSQLDB: there is the
> DATEDIFF function, but it lacks of other important
> function present in
> important RDBMS: DATE_ADD, DATE_FORMAT, etc.
> Besides, the string formating of dates is made by
> default to en-US [1].
>
> I found a solution for my personal needs, let me
> tell you:
>
> In the Spanish mailing list a user asked if OOoBase
> (that is HSQLDB as
> embedded engine) has functions to get the age and
> add time intervals
> (like DATE_ADD in some RDBMS). For the age question
> I found a workaround
> using a set of SQL functions, but not for the
> DATE_ADD.
>
> After looking for a workaround sending a message to
> this mailing list
>
>
http://dba.openoffice.org/servlets/ReadMsg?listName=users&msgNo=4584
>
> and to HSQLDB mailing list
>
>
http://article.gmane.org/gmane.comp.java.hsqldb.user/3621
>
http://article.gmane.org/gmane.comp.java.hsqldb.user/3622
>
>
> and as the only answer I got was to read the manual
> ("It's in the
> manual: write a public static function in Java and
> then
> CERATE ALIAS." - BUT of course I DID read it before
> sending the mail!!!
> It's just like telling all the users in the OOoBase
> mailing list: "You,
> STUPID, just press F1 and read the help files!!!")
>
>
> after all this I wrote my own SQL functions in Java:
>
> EDAD( Date adate ) ["EDAD"== AGE]
> DATEADD( String intervalCode, int interval,
> Date adate )
> TIMEADD ( String intervalCode, int interval,
> Timestamp atimestamp)
>
> in this last case, I had to split the DATE_ADD
> function in two; because,
> although the Java programming language allows to
> OVERLOAD methods, when
> there are two methods with same name and number of
> parameters (although
> they have different type), HSQLDB invokes only the
> first one that
> founds (another solution would have been to add a
> param. and keep the
> same name for both static methods).
>
> And, as HSQLDB for ALL the date/time functions that
> imply some
> formatting, uses the ENGLISH LOCALE (so MONTHNAME()
> etc. return english
> names), I wrote
>
> FECHA ( String formato, Date/Timestamp
> fecha) == DATE_FORMAT
> DIA( Date/Timestamp fecha ) == DAY_NAME
> MES( Date/Timestamp fecha ) == MONTH_NAME
>
> FECHA ( formato, Date/Timestamp fecha) uses
> java.text.DateFormat fields
> SHORT, MEDIUM, LONG, FULL to format the output of
> date/Timestamp fields
> in a locale-sensitive-JVM-dependent way.
>
> I send you the JAR file and an ODB to test it.
> First, you have to add
> the JAR to your classpath (Tools â Options â OOo
> â Java â Class path ),
> then open the ODB and test it.
>
> In the ODB there is a "veraltet" LEAME (read-me) in
> Spanish. The queries
> have comments also in Spanish.
>
>
>
> > 1989-09-24 1990-01-01 +99 OK
> > 1989-09-25 1990-01-02 +99 OK
> > 1989-09-26 1990-01-03 +99 OK
> > 1989-09-27 1990-01-04 +99 OK
> > 1989-09-28 1990-01-05 +99 OK
> > 1989-09-29 1990-01-06 +99 OK
> > 1989-09-30 1990-01-07 +99 OK
> > 1989-10-01 1989-10-10 +9 here it starts failing
> > 1989-10-02 1989-10-10 +8
> > ... weird sequence of offsets
> > 1989-12-31 1989-12-13 -18
> > 1990-01-01 1990-04-10 +99 OK, same year
> > ... OK
>
> my functions work "quite" well here ...
>
> >
> > I proved the calculated day offset in Calc and
> with DateDiff('dd',
> > "Date", "CalculatedDate").
> >
> > Surprisingly, adding days across years works
> within the first millenium.
> > The smallest date we can enter is 100-01-01. Two
> digit years get
> > converted according to OOo's two-digit-date
> options.
> > I'm using Sun's OOo2.2 on Linux.
> >
> > So the question is: How to calculate day-offsets
> in a reliable manner?
> > I hope that I could help Alberto Muller with a
> method for year-offsets,
> > which seem to work mostly. I found one issue with
> 3-digit years:
> > 100-01-01 +3years --> 102-12-31 which is -1 day
> off, same with months.
>
> I think this is a Java issue, see the test in my
> base
>
>
> What I think is an OOo [I don't know if Base's or
> OOo's date formatting]
> issue is in the table DATE_TEST of my base, fields
> id 21 and 21, when I
> type 01/01/101, the fields turn into 31/12/100.
>
>
> Finally, I cannot explain you here how the functions
> work (I will have
> to write an endless message), but I tried to make
> them intuitive (i.e.
> ++/-- 30 days isn't the same as ++/-- 1 month, for
> there are months with
> 28/29/30/31 days; the sames for ++/-- 365 years -
> ++/-- 1 year, etc.)
>
>
> Bye and luck,
>
>
> --
> Ariel Constenla-Haile
> La Plata, Argentina
>
> [EMAIL PROTECTED]
> [EMAIL PROTECTED]
>
> http://www.arielconstenlahaile.com.ar/ooo/
>
>
> >
---------------------------------------------------------------------
> To unsubscribe, e-mail:
> [EMAIL PROTECTED]
> For additional commands, e-mail:
[EMAIL PROTECTED]
"En el jardin de tu corazón, no plantes sino la rosa del amor." Bahá´u´lláh
Fundación Anisa, A.C. www.fundacionanisa.org ; [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
