Fernand, Well I also use EXTRACT(), but have found that I still have to CAST the type. If I just use the DATE/TIME functions on their own, the results are inconsistent across the months, especially when adding or subtracting. I haven't kept any of my old queries that did direct application of date addition / substraction because they were of no use to me, so I rewrote them all with CAST and EXTRACT.
An example of a current one that works is given below : SELECT CAST(EXTRACT(YEAR FROM CURDATE()) as SIGNED)-CAST(EXTRACT(YEAR FROM Date1) as SIGNED)+1 as 'Renewal Num' FROM mybase where MONTH(Date1) = 12 I do not consider this to be an optimised way of obtaining the result I wanted, but I found it to be the only way that worked reliably for each month of the year, independently of the date provided by CURRENT_DATE. In a more simplified expression, the results were wrong by +/- 1 dependent on the MONTH given and the date provided by CURRENT_DATE. It may be that the underlying cause to this is a problem with date handling by Mysql, and not OOo at all, but I haven't checked this out. Alex ----- "Fernand Vanrie" <s...@pmgroup.be> a écrit : > Alex , > > its also a fact that there are (better) alternatives for cast Date() > > convert() or even left() and right() > > Hi Fernand, > > > > Done, but whether it will make any difference ? Its not a blocker > because it doesn't cause a crash, or lose your data, but it is surely > a regression from 3.2. > > > > > > Alex > > > > > > > > ----- "Fernand Vanrie"<s...@pmgroup.be> a écrit : > > > >> Alexander , > >> > >> It sould be good to send this message also to > relea...@openoffice.org > >> > >> there they decide over live and dead :-) > >> > >> greetz > >> > >> Fernand > >>> Hi all, > >>> > >>> Le 08/12/10 13:38, Reizinger Zoltán a écrit : > >>>> Hi Fernand, > >>>> It feels to me as I touched in some days ago, and find it, as a > >> known > >>>> issue with cast in MySQL: > >>>> http://qa.openoffice.org/issues/show_bug.cgi?id=115436 > >>>> Zoltan > >>>> > >>> Hmm, this would not be good for me if this is the case because I > use > >> a > >>> lot of cast statements with date values in mysql. The reason is > >> simple, > >>> default typing of the values in date strings used to lead to > funny > >>> behaviour for me on OOo, which meant that calculations I had in > my > >>> queries based on the default types led to incorrect results. I > was > >> thus > >>> forced into using CAST to ensure correct handling. If that > >> functionality > >>> has now gone away with the latest dev release, whereas it works > in > >> 3.2, > >>> and connector 1.0.0, I see no incentive to move to 3.3. > >>> > >>> Alex > >>> > >>> > >>> > >> > --------------------------------------------------------------------- > >>> To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org > >>> For additional commands, e-mail: dev-h...@dba.openoffice.org > >> > >> > --------------------------------------------------------------------- > >> To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org > >> For additional commands, e-mail: dev-h...@dba.openoffice.org > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org > > For additional commands, e-mail: dev-h...@dba.openoffice.org > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org > For additional commands, e-mail: dev-h...@dba.openoffice.org --------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
