Code: Select all
SELECT "Date_in", "Money", "Source" FROM "Payments" WHERE "Date_in" > dateadd('mm',-3,curdate())
using lastest AOO
thx in advance
Code: Select all
SELECT "Date_in", "Money", "Source" FROM "Payments" WHERE "Date_in" > dateadd('mm',-3,curdate())
Code: Select all
SELECT "Date_in", "Money", "Source" FROM "Payments" WHERE DATEDIFF('mm',"Date_In",NOW()) > 3
Yes.arfgh wrote:are you saying that DATEADD isnt implemented on AOO SQL ?
No, but for single-user access you can dump backend, frontend and driver in one folder and use my FreeHSQLDB to build transferable zip archives with an auto-installer macro.arfgh wrote:also embedded without to have to split things ?
Does not work? I think so, so it might be enough…charlie.it wrote:Ciao, try:
Code: Select all
SELECT "Date_in", "Money", "Source" FROM "Payments" WHERE DATEDIFF('mm',"Date_In",NOW()) > 3
Is there any reason why you do not check out my database package?arfgh wrote:but using all that you are explaining, i have to reject the use of AOO Base ?
Code: Select all
--adds one month to a date field
--produces a result equivalent to the Calc formula '=DATE(YEAR(A2);MONTH(A2)+1;DAY(A2))'
select
"Date",
cast(
case
when
month("Date")=12 --going up 1 year. month will be january & have 31 days
then
year("Date")+1 || '-01-' || right('0' || day("Date"), 2)
when
month("Date")=1 and mod(year("Date"), 4) <> 0 --going into febuary no leap year (max 28 days)
then
year("Date") || '-0' ||
casewhen(day("Date") <= 28,
'2-' || right('0' || day("Date"), 2),
'3-0' || day("Date")-28)
when
month("Date")=1 and mod(year("Date"), 4) = 0 --going into febuary and a leap year (max 29 days)
then
year("Date") || '-0' ||
casewhen(day("Date") <= 29,
'2-' || right('0' || day("Date"), 2),
'3-0' || day("Date")-29)
when
month("Date")=3 or month("Date")=5 or month("Date")=8 or month("Date")=10 --next month has max 30 days
then
year("Date") || '-' ||
casewhen(day("Date") <= 30,
right('0' || month("Date")+1, 2) || '-' || right('0' || day("Date"), 2),
right('0' || month("Date")+2, 2) || '-01')
else --month = 2, 4, 6, 7, 9 or 11. next month has 31 days
year("Date") || '-' || right('0' || month("Date")+1, 2) || '-' || right('0' || day("Date"), 2)
end
as DATE)
as "Date+1Month"
from
"tMyTable"