[libreoffice-users] Re: LO Base - calculating dates
Am 30.01.2012 09:58, Mark Stanton wrote: Use MySQL with PERIOD_ADD(P,N) to add N months to the date P Mark Stanton One small step for mankind... This is why I always ask for the database engine (without ever getting an answer). -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LO Base - calculating dates
Am 28.01.2012 23:54, Dan Lewis wrote: Does someone know how to create a table using SQL or perhaps even a macro that would create the date table with enough entries for the number of years that this database might need? --Dan Use a spreadsheet where the DATE function in combination of MOD, INT and others can generate arbitrary sequences. Select your sequence, copy, select the table icon in the main database window and paste (or drag the cells onto the icon). Follow the import wizard. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LO Base - calculating dates
Le 28/01/2012 23:54, Dan Lewis a écrit : Hi Dan, Does someone know how to create a table using SQL or perhaps even a macro that would create the date table with enough entries for the number of years that this database might need? At a guess, I'd say this problem has probably come up on one of the OOo forums. Alex -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LO Base - calculating dates
Am 28.01.2012 23:54, Dan Lewis wrote:
This might not be the most elegant solution:
I created a table of dates (01/01/2012 to 12/31/2013) calling it "Date".
Then I wrote a query using the following SQL:
SELECT "Date" FROM "Date" WHERE DATEDIFF('dd',:start_date,"Date")
=180 OR DATEDIFF('dd',:start_date,"Date") = 365
IF and only IF we are talking about the infamous HSQLDB 1.8 which is
embedded in the so called "Base document", the following trick works
with years but not nesecarily with months and days:
SELECT CAST(YEAR("Date")+1||'-'||
CASEWHEN(MONTH("Date")<10,'0','')||MONTH("Date") ||'-'||
CASEWHEN((DAY("Date"))<10,'0','')||(DAY("Date")) AS DATE)AS"Calc Date"
FROM "Table"
--
For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: LO Base - calculating dates
On Sat, 2012-01-28 at 21:56 +0100, Andreas Säger wrote:
> Base is not a database. Base is a tool to work with variuos types of
> databases. The type of database connected to your Base document is
> indicated in the status bar of the database main window. If you are
> talking about an embedded HSQLDB then all I can tell is, that there is
> no built-in way to calculate dates other than CURRENT_DATE. "Better"
> databases have a function DATEADD at least.
> Which type of database are you connected to?
>
>
This might not be the most elegant solution:
I created a table of dates (01/01/2012 to 12/31/2013) calling it "Date".
Then I wrote a query using the following SQL:
SELECT "Date" FROM "Date" WHERE DATEDIFF('dd',:start_date,"Date")
=180 OR DATEDIFF('dd',:start_date,"Date") = 365
:start_date is a parameter. When the query is run, a dialog asks you for
the start date. It then returns the date 180 days later followed by the
date 365 days later.
Does someone know how to create a table using SQL or perhaps even a
macro that would create the date table with enough entries for the
number of years that this database might need?
--Dan
--
For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LO Base - calculating dates
Base is not a database. Base is a tool to work with variuos types of databases. The type of database connected to your Base document is indicated in the status bar of the database main window. If you are talking about an embedded HSQLDB then all I can tell is, that there is no built-in way to calculate dates other than CURRENT_DATE. "Better" databases have a function DATEADD at least. Which type of database are you connected to? -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
