File indexing completed on 2024-05-12 16:35:54
0001 /* This file is part of the KDE project 0002 Copyright 2007 Sascha Pfau <MrPeacock@web.de> 0003 0004 This library is free software; you can redistribute it and/or 0005 modify it under the terms of the GNU Library General Public 0006 License as published by the Free Software Foundation; only 0007 version 2 of the License. 0008 0009 This library is distributed in the hope that it will be useful, 0010 but WITHOUT ANY WARRANTY; without even the implied warranty of 0011 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 0012 Library General Public License for more details. 0013 0014 You should have received a copy of the GNU Library General Public License 0015 along with this library; see the file COPYING.LIB. If not, write to 0016 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, 0017 Boston, MA 02110-1301, USA. 0018 */ 0019 #include "TestDatetimeFunctions.h" 0020 0021 #include "TestKspreadCommon.h" 0022 0023 #include <KLocale> 0024 0025 void TestDatetimeFunctions::initTestCase() 0026 { 0027 FunctionModuleRegistry::instance()->loadFunctionModules(); 0028 } 0029 0030 #define CHECK_EVAL(x,y) { Value z(RoundNumber(y)); QCOMPARE(evaluate(x,z), (z)); } 0031 0032 #define CHECK_FAIL(x,y,txt) { Value z(RoundNumber(y)); QEXPECT_FAIL("", txt, Continue); QCOMPARE(evaluate(x,z), (z));} 0033 #define ROUND(x) (roundf(1e10 * x) / 1e10) 0034 0035 // changelog 0036 ///////////////////////////////////// 0037 // 18.05.07 0038 // - fix typo in yearfrac 0039 // - indent 0040 // - added missing tests EOMONTH() 0041 // - added missing values in DATEDIF 0042 // 02.06.07 0043 // - added Isoweeknum tests starts on sunday 0044 // - added WEEKINYEAR unittests 0045 // - added ISLEAPYEAR unittests 0046 // - added DAYSINMONTH unittests 0047 // 15.07.07 0048 // - modified YEARFRAC basis=1 0049 // 30.10.07 0050 // - fixed WEEKNUM tests 0051 // - corrected wrong DAYS360,EDATE and EOMONTH unittests 0052 // - commented out last issue on YEARFRAC 0053 0054 #if 0 // not used? 0055 // round to get at most 10-digits number 0056 static Value RoundNumber(double f) 0057 { 0058 return Value(ROUND(f)); 0059 } 0060 #endif 0061 0062 // round to get at most 10-digits number 0063 static Value RoundNumber(const Value& v) 0064 { 0065 if (v.isNumber()) { 0066 double d = numToDouble(v.asFloat()); 0067 if (fabs(d) < DBL_EPSILON) 0068 d = 0.0; 0069 return Value(ROUND(d)); 0070 } else 0071 return v; 0072 } 0073 0074 Value TestDatetimeFunctions::evaluate(const QString& formula, Value& ex) 0075 { 0076 Formula f; 0077 QString expr = formula; 0078 if (expr[0] != '=') 0079 expr.prepend('='); 0080 f.setExpression(expr); 0081 Value result = f.eval(); 0082 0083 if (result.isFloat() && ex.isInteger()) 0084 ex = Value(ex.asFloat()); 0085 if (result.isInteger() && ex.isFloat()) 0086 result = Value(result.asFloat()); 0087 0088 return RoundNumber(result); 0089 } 0090 0091 void TestDatetimeFunctions::testYEARFRAC() 0092 { 0093 0094 // basis 0 US 0095 CHECK_EVAL("YEARFRAC( \"1999-01-01\" ; \"1999-06-30\" ; 0)", Value(0.4972222222)); 0096 CHECK_EVAL("YEARFRAC( \"1999-01-01\" ; \"1999-07-01\" ; 0)", Value(0.5000000000)); 0097 CHECK_EVAL("YEARFRAC( \"2000-01-01\" ; \"2000-06-30\" ; 0)", Value(0.4972222222)); 0098 CHECK_EVAL("YEARFRAC( \"2000-01-15\" ; \"2000-09-17\" ; 0)", Value(0.6722222222)); 0099 CHECK_EVAL("YEARFRAC( \"2000-01-01\" ; \"2001-01-01\" ; 0)", Value(1.0000000000)); 0100 CHECK_EVAL("YEARFRAC( \"2001-01-01\" ; \"2002-01-01\" ; 0)", Value(1.0000000000)); 0101 CHECK_EVAL("YEARFRAC( \"2001-12-05\" ; \"2001-12-30\" ; 0)", Value(0.0694444444)); 0102 CHECK_EVAL("YEARFRAC( \"2000-02-05\" ; \"2006-08-10\" ; 0)", Value(6.5138888889)); 0103 0104 // basis 0 is default 0105 CHECK_EVAL("YEARFRAC( \"1999-01-01\" ; \"1999-06-30\")", Value(0.4972222222)); 0106 CHECK_EVAL("YEARFRAC( \"1999-01-01\" ; \"1999-07-01\")", Value(0.5000000000)); 0107 CHECK_EVAL("YEARFRAC( \"2000-01-01\" ; \"2000-06-30\")", Value(0.4972222222)); 0108 CHECK_EVAL("YEARFRAC( \"2000-01-15\" ; \"2000-09-17\")", Value(0.6722222222)); 0109 CHECK_EVAL("YEARFRAC( \"2000-01-01\" ; \"2001-01-01\")", Value(1.0000000000)); 0110 CHECK_EVAL("YEARFRAC( \"2001-01-01\" ; \"2002-01-01\")", Value(1.0000000000)); 0111 CHECK_EVAL("YEARFRAC( \"2001-12-05\" ; \"2001-12-30\")", Value(0.0694444444)); 0112 CHECK_EVAL("YEARFRAC( \"2000-02-05\" ; \"2006-08-10\")", Value(6.5138888889)); 0113 0114 // basis 1 Actual/actual 0115 // other values are taken from OOo-2.2.1 0116 CHECK_EVAL("YEARFRAC( \"1999-01-01\" ; \"1999-06-30\" ; 1)", Value(0.4931506849)); 0117 CHECK_EVAL("YEARFRAC( \"1999-01-01\" ; \"1999-07-01\" ; 1)", Value(0.4958904110)); 0118 CHECK_EVAL("YEARFRAC( \"2000-01-01\" ; \"2000-06-30\" ; 1)", Value(0.4945355191)); 0119 CHECK_EVAL("YEARFRAC( \"2000-01-15\" ; \"2000-09-17\" ; 1)", Value(0.6721311475)); 0120 CHECK_EVAL("YEARFRAC( \"2000-01-01\" ; \"2001-01-01\" ; 1)", Value(1.0000000000)); 0121 CHECK_EVAL("YEARFRAC( \"2001-12-05\" ; \"2001-12-30\" ; 1)", Value(0.0684931507)); 0122 CHECK_EVAL("YEARFRAC( \"2000-02-05\" ; \"2006-08-10\" ; 1)", Value(6.5099726242)); // specs 6.5099726242 OOo-2.3.0 6.5081967213 0123 CHECK_EVAL("YEARFRAC( \"2003-12-06\" ; \"2004-03-05\" ; 1)", Value(0.2459016393)); 0124 CHECK_EVAL("YEARFRAC( \"2003-12-31\" ; \"2004-03-31\" ; 1)", Value(0.2486338798)); 0125 CHECK_EVAL("YEARFRAC( \"2004-10-01\" ; \"2005-01-11\" ; 1)", Value(0.2794520548)); 0126 CHECK_EVAL("YEARFRAC( \"2004-10-26\" ; \"2005-02-06\" ; 1)", Value(0.2821917808)); 0127 CHECK_EVAL("YEARFRAC( \"2004-11-20\" ; \"2005-03-04\" ; 1)", Value(0.2849315068)); 0128 CHECK_EVAL("YEARFRAC( \"2004-12-15\" ; \"2005-03-30\" ; 1)", Value(0.2876712329)); 0129 CHECK_EVAL("YEARFRAC( \"2000-12-01\" ; \"2001-01-16\" ; 1)", Value(0.1260273973)); 0130 CHECK_EVAL("YEARFRAC( \"2000-12-26\" ; \"2001-02-11\" ; 1)", Value(0.1287671233)); 0131 0132 // basis 2 Actual/360 0133 CHECK_EVAL("YEARFRAC( \"2000-01-01\" ; \"2000-06-30\" ; 2)", Value(0.5027777778)); 0134 CHECK_EVAL("YEARFRAC( \"1999-01-01\" ; \"1999-06-30\" ; 2)", Value(0.5000000000)); 0135 CHECK_EVAL("YEARFRAC( \"1999-01-01\" ; \"1999-07-01\" ; 2)", Value(0.5027777778)); 0136 CHECK_EVAL("YEARFRAC( \"2000-01-15\" ; \"2000-09-17\" ; 2)", Value(0.6833333333)); 0137 CHECK_EVAL("YEARFRAC( \"2000-01-01\" ; \"2001-01-01\" ; 2)", Value(1.0166666667)); 0138 CHECK_EVAL("YEARFRAC( \"2001-01-01\" ; \"2002-01-01\" ; 2)", Value(1.0138888889)); 0139 CHECK_EVAL("YEARFRAC( \"2001-12-05\" ; \"2001-12-30\" ; 2)", Value(0.0694444444)); 0140 CHECK_EVAL("YEARFRAC( \"2000-02-05\" ; \"2006-08-10\" ; 2)", Value(6.6055555556)); 0141 0142 // basis 3 Actual/365 0143 CHECK_EVAL("YEARFRAC( \"2000-01-01\" ; \"2001-01-01\" ; 3)", Value(1.0027397260)); 0144 CHECK_EVAL("YEARFRAC( \"2001-01-01\" ; \"2002-01-01\" ; 3)", Value(1.0000000000)); 0145 CHECK_EVAL("YEARFRAC( \"2001-12-05\" ; \"2001-12-30\" ; 3)", Value(0.0684931507)); 0146 CHECK_EVAL("YEARFRAC( \"2000-02-05\" ; \"2006-08-10\" ; 3)", Value(6.5150684932)); 0147 0148 // basis 4 European 30/360 0149 CHECK_EVAL("YEARFRAC( \"1999-01-01\" ; \"1999-06-30\" ; 4)", Value(0.4972222222)); 0150 CHECK_EVAL("YEARFRAC( \"2000-01-01\" ; \"2000-06-30\" ; 4)", Value(0.4972222222)); 0151 CHECK_EVAL("YEARFRAC( \"2000-01-15\" ; \"2000-09-17\" ; 4)", Value(0.6722222222)); 0152 CHECK_EVAL("YEARFRAC( \"2000-01-01\" ; \"2001-01-01\" ; 4)", Value(1.0000000000)); 0153 CHECK_EVAL("YEARFRAC( \"2001-01-01\" ; \"2002-01-01\" ; 4)", Value(1.0000000000)); 0154 CHECK_EVAL("YEARFRAC( \"2001-12-05\" ; \"2001-12-30\" ; 4)", Value(0.0694444444)); 0155 CHECK_EVAL("YEARFRAC( \"2000-02-05\" ; \"2006-08-10\" ; 4)", Value(6.5138888889)); 0156 0157 // alternate function name 0158 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETYEARFRAC(\"1999-01-01\";\"1999-06-30\";1)", Value(0.4931506849)); 0159 } 0160 0161 void TestDatetimeFunctions::testDATEDIF() 0162 { 0163 // interval y ( years ) 0164 CHECK_EVAL("DATEDIF(DATE(1990;2;15); DATE(1993;9;15); \"y\")", Value(3)); // TODO check value; kspread says 3 0165 0166 // interval m ( Months. If there is not a complete month between the dates, 0 will be returned.) 0167 CHECK_EVAL("DATEDIF(DATE(1990;2;15); DATE(1993;9;15); \"m\")", Value(43)); 0168 0169 // interval d ( Days ) 0170 CHECK_EVAL("DATEDIF(DATE(1990;2;15); DATE(1993;9;15); \"d\")", Value(1308)); // TODO check value; kspread says 1308 0171 0172 // interval md ( Days, ignoring months and years ) 0173 CHECK_EVAL("DATEDIF(DATE(1990;2;15); DATE(1993;9;15); \"md\")", Value(0)); 0174 0175 // interval ym ( Months, ignoring years ) 0176 CHECK_EVAL("DATEDIF(DATE(1990;2;15); DATE(1993;9;15); \"ym\")", Value(7)); 0177 0178 // interval yd ( Days, ignoring years ) 0179 CHECK_EVAL("DATEDIF(DATE(1990;2;15); DATE(1993;9;15); \"yd\")", Value(212)); // TODO check value; kspread says 212 0180 } 0181 0182 void TestDatetimeFunctions::testISLEAPYEAR() 0183 { 0184 // only every 400 years ... 0185 CHECK_EVAL("ISLEAPYEAR(1900)", Value(false)); 0186 CHECK_EVAL("ISLEAPYEAR(2000)", Value(true)); 0187 CHECK_EVAL("ISLEAPYEAR(2100)", Value(false)); 0188 CHECK_EVAL("ISLEAPYEAR(2200)", Value(false)); 0189 CHECK_EVAL("ISLEAPYEAR(2300)", Value(false)); 0190 CHECK_EVAL("ISLEAPYEAR(2400)", Value(true)); 0191 CHECK_EVAL("ISLEAPYEAR(1900)", Value(false)); 0192 // and every 4th year 0193 CHECK_EVAL("ISLEAPYEAR(2000)", Value(true)); 0194 CHECK_EVAL("ISLEAPYEAR(2001)", Value(false)); 0195 CHECK_EVAL("ISLEAPYEAR(2002)", Value(false)); 0196 CHECK_EVAL("ISLEAPYEAR(2003)", Value(false)); 0197 CHECK_EVAL("ISLEAPYEAR(2004)", Value(true)); 0198 // test alternate name for the ISLEAPYEAR function 0199 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETISLEAPYEAR(1900)", Value(false)); 0200 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETISLEAPYEAR(2000)", Value(true)); 0201 } 0202 0203 void TestDatetimeFunctions::testWEEKNUM() 0204 { 0205 // is known as weeknum_add() in OOo 0206 0207 // type default ( type 1 ) 0208 CHECK_EVAL("WEEKNUM(DATE(2000;05;21))", Value(22)); // 0209 CHECK_EVAL("WEEKNUM(DATE(2005;01;01))", Value(01)); // 0210 CHECK_EVAL("WEEKNUM(DATE(2000;01;02))", Value(02)); // 0211 CHECK_EVAL("WEEKNUM(DATE(2000;01;03))", Value(02)); // 0212 CHECK_EVAL("WEEKNUM(DATE(2000;01;04))", Value(02)); // 0213 CHECK_EVAL("WEEKNUM(DATE(2006;01;01))", Value(01)); // 0214 0215 // type 1 0216 CHECK_EVAL("WEEKNUM(DATE(2000;05;21);1)", Value(22)); 0217 CHECK_EVAL("WEEKNUM(DATE(2008;03;09);1)", Value(11)); 0218 0219 // type 2 0220 CHECK_EVAL("WEEKNUM(DATE(2000;05;21);2)", Value(21)); 0221 CHECK_EVAL("WEEKNUM(DATE(2005;01;01);2)", Value(01)); // ref. OOo-2.2.0 = 1 0222 CHECK_EVAL("WEEKNUM(DATE(2000;01;02);2)", Value(01)); // ref. OOo-2.2.0 = 1 0223 CHECK_EVAL("WEEKNUM(DATE(2000;01;03);2)", Value(02)); // ref. OOo-2.2.0 = 2 0224 CHECK_EVAL("WEEKNUM(DATE(2000;01;04);2)", Value(02)); // ref. OOo-2.2.0 = 2 0225 CHECK_EVAL("WEEKNUM(DATE(2008;03;09);2)", Value(10)); 0226 0227 // additional tests for method 2 0228 CHECK_EVAL("WEEKNUM(DATE(2006;01;01);2)", Value(01)); 0229 CHECK_EVAL("WEEKNUM(DATE(2006;01;02);2)", Value(02)); 0230 0231 // alternate function name 0232 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETWEEKNUM(DATE(2000;05;21);1)", Value(22)); 0233 } 0234 0235 void TestDatetimeFunctions::testWEEKSINYEAR() 0236 { 0237 CHECK_EVAL("WEEKSINYEAR(1970)", Value(53)); 0238 CHECK_EVAL("WEEKSINYEAR(1995)", Value(52)); 0239 CHECK_EVAL("WEEKSINYEAR(2009)", Value(53)); 0240 CHECK_EVAL("WEEKSINYEAR(2010)", Value(52)); 0241 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETWEEKSINYEAR(1992)", Value(53)); 0242 } 0243 0244 void TestDatetimeFunctions::testWORKDAY() 0245 { 0246 // 2001 JAN 01 02 03 04 05 06 07 08 0247 // MO TU WE TH FR SA SU MO 0248 // 01 02 -- -- 0249 CHECK_EVAL("WORKDAY(DATE(2001;01;01);2;2)=DATE(2001;01;05)", Value(true)); 0250 CHECK_EVAL("WORKDAY(DATE(2001;01;01);2;3)=DATE(2001;01;08)", Value(true)); 0251 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETWORKDAY(DATE(2001;01;01);2;3)=DATE(2001;01;08)", Value(true)); 0252 } 0253 0254 void TestDatetimeFunctions::testNETWORKDAY() 0255 { 0256 // 2001 JAN 01 02 03 04 05 06 07 08 09 0257 // MO TU WE TH FR SA SU MO TU 0258 // 01 02 03 04 05 05 05 06 ... networkdays 0259 CHECK_EVAL("NETWORKDAY(DATE(2001;01;01);DATE(2001;01;08))", Value(5)); 0260 CHECK_EVAL("NETWORKDAY(DATE(2001;01;01);DATE(2001;01;07))", Value(5)); 0261 CHECK_EVAL("NETWORKDAY(DATE(2001;01;01);DATE(2001;01;06))", Value(5)); 0262 CHECK_EVAL("NETWORKDAY(DATE(2001;01;01);DATE(2001;01;05))", Value(4)); 0263 0264 // 2008 FEB 25 26 27 28 29 01 02 03 04 0265 // MO TU WE TH FR SA SU MO TU 0266 // 01 02 03 04 05 05 05 06 ... networkdays 0267 CHECK_EVAL("NETWORKDAY(DATE(2008;02;25);DATE(2008;02;28))", Value(3)); 0268 CHECK_EVAL("NETWORKDAY(DATE(2008;02;25);DATE(2008;02;29))", Value(4)); 0269 CHECK_EVAL("NETWORKDAY(DATE(2008;02;25);DATE(2008;03;01))", Value(5)); 0270 CHECK_EVAL("NETWORKDAY(DATE(2008;02;25);DATE(2008;03;02))", Value(5)); 0271 CHECK_EVAL("NETWORKDAY(DATE(2008;02;25);DATE(2008;03;03))", Value(5)); 0272 CHECK_EVAL("NETWORKDAY(DATE(2008;02;25);DATE(2008;03;04))", Value(6)); 0273 } 0274 0275 void TestDatetimeFunctions::testUNIX2DATE() 0276 { 0277 // 01/01/2001 = 946684800 0278 CHECK_EVAL("UNIX2DATE(946684800)=DATE(2000;01;01)", Value(true)); // TODO result of various unix-timestamp calculator is 946681200 (UTC?) 0279 } 0280 0281 void TestDatetimeFunctions::testDATE2UNIX() 0282 { 0283 // 946681200 = 01/01/2001 0284 CHECK_EVAL("DATE2UNIX(DATE(2000;01;01))=946684800", Value(true)); // TODO 0285 } 0286 0287 void TestDatetimeFunctions::testDATE() 0288 { 0289 // 0290 CHECK_EVAL("DATE(2005;12;31)-DATE(1904;01;01)", Value(37255)); 0291 CHECK_EVAL("DATE(2004;02;29)=DATE(2004;02;28)+1", Value(true)); // leap year 0292 CHECK_EVAL("DATE(2000;02;29)=DATE(2000;02;28)+1", Value(true)); // leap year 0293 CHECK_EVAL("DATE(2005;03;01)=DATE(2005;02;28)+1", Value(true)); // no leap year 0294 CHECK_EVAL("DATE(2017.5;01;02)=DATE(2017;01;02)", Value(true)); // fractional values for year are truncated 0295 CHECK_EVAL("DATE(2006; 2.5; 3)=DATE(2006; 2; 3)", Value(true)); // fractional values for month are truncated 0296 CHECK_EVAL("DATE(2006;01;03.5)=DATE(2006;01;03)", Value(true)); // fractional values for day are truncated 0297 CHECK_EVAL("DATE(2006;13;03)=DATE(2007;01;03)", Value(true)); // months > 12 roll over to year 0298 CHECK_EVAL("DATE(2006;01;32)=DATE(2006;02;01)", Value(true)); // days greater than month limit roll over to month 0299 CHECK_EVAL("DATE(2006;25;34)=DATE(2008;02;03)", Value(true)); // days and months roll over transitively 0300 CHECK_EVAL("DATE(2006;-01;01)=DATE(2005;11;01)", Value(true)); // negative months roll year backward 0301 CHECK_EVAL("DATE(2006;04;-01)=DATE(2006;03;30)", Value(true)); // negative days roll month backward 0302 CHECK_EVAL("DATE(2006;-4;-1)=DATE(2005;07;30)", Value(true)); // negative days and months roll backward transitively 0303 CHECK_EVAL("DATE(2003;2;29)=DATE(2003;03;01)", Value(true)); // non-leap year rolls forward 0304 } 0305 0306 void TestDatetimeFunctions::testDATEVALUE() 0307 { 0308 // 0309 CHECK_EVAL("DATEVALUE(\"2004-12-25\")=DATE(2004;12;25)", Value(true)); 0310 } 0311 0312 void TestDatetimeFunctions::testDAY() 0313 { 0314 // 0315 CHECK_EVAL("DAY(DATE(2006;05;21))", Value(21)); 0316 CHECK_EVAL("DAY(\"2006-12-15\")", Value(15)); 0317 } 0318 0319 void TestDatetimeFunctions::testDAYS() 0320 { 0321 // 0322 CHECK_EVAL("DAYS(DATE(1993;4;16); DATE(1993;9;25))", Value(-162)); // 0323 } 0324 0325 void TestDatetimeFunctions::testDAYSINMONTH() 0326 { 0327 // non leapyear 0328 CHECK_EVAL("DAYSINMONTH(1995;01)", Value(31)); 0329 CHECK_EVAL("DAYSINMONTH(1995;02)", Value(28)); 0330 CHECK_EVAL("DAYSINMONTH(1995;03)", Value(31)); 0331 CHECK_EVAL("DAYSINMONTH(1995;04)", Value(30)); 0332 CHECK_EVAL("DAYSINMONTH(1995;05)", Value(31)); 0333 CHECK_EVAL("DAYSINMONTH(1995;06)", Value(30)); 0334 CHECK_EVAL("DAYSINMONTH(1995;07)", Value(31)); 0335 CHECK_EVAL("DAYSINMONTH(1995;08)", Value(31)); 0336 CHECK_EVAL("DAYSINMONTH(1995;09)", Value(30)); 0337 CHECK_EVAL("DAYSINMONTH(1995;10)", Value(31)); 0338 CHECK_EVAL("DAYSINMONTH(1995;11)", Value(30)); 0339 CHECK_EVAL("DAYSINMONTH(1995;12)", Value(31)); 0340 0341 // leapyear 0342 CHECK_EVAL("DAYSINMONTH(2000;02)", Value(29)); 0343 CHECK_EVAL("DAYSINMONTH(1900;02)", Value(28)); // non leapyear 0344 CHECK_EVAL("DAYSINMONTH(2004;02)", Value(29)); 0345 0346 // test alternate name for the DAYSINMONTH function 0347 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETDAYSINMONTH(1995;01)", Value(31)); // alternate function name 0348 } 0349 0350 void TestDatetimeFunctions::testDAYSINYEAR() 0351 { 0352 CHECK_EVAL("DAYSINYEAR(2000)", Value(366)); 0353 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETDAYSINYEAR(2000)", Value(366)); // alternate function name 0354 } 0355 0356 void TestDatetimeFunctions::testDAYS360() 0357 { 0358 // TODO Note: Lotus 1-2-3v9.8 has a function named DAYS but with different semantics. It supports an optional "Basis" parameter 0359 // with many different options. Without the optional parameter, it defaults to a 30/360 basis, not calendar days; thus, in Lotus 1-2-3v9.8, 0360 // DAYS(DATE(1993;4;16); DATE(1993;9;25)) computes -159, not -162. 0361 0362 CHECK_EVAL("DAYS360(DATE(1993;4;16);DATE(1993;9;25); FALSE)", Value(159)); // specs. -162 but OOo and KSpread calculate 159 0363 CHECK_EVAL("DAYS360(\"2002-02-22\"; \"2002-04-21\" ; FALSE)", Value(59)); // ref. docs 0364 } 0365 0366 void TestDatetimeFunctions::testEDATE() 0367 { 0368 // 0369 CHECK_EVAL("EDATE(\"2006-01-01\";0) =DATE(2006;01;01)", Value(true)); // If zero, unchanged. 0370 CHECK_EVAL("EDATE(DATE(2006;01;01);0)=DATE(2006;01;01)", Value(true)); // You can pass strings or serial numbers to EDATE 0371 CHECK_EVAL("EDATE(\"2006-01-01\"; 2) =DATE(2006;03;01)", Value(true)); // 0372 CHECK_EVAL("EDATE(\"2006-01-01\";-2) =DATE(2005;11;01)", Value(true)); // 2006 is not a leap year. Last day of March, going back to February 0373 CHECK_EVAL("EDATE(\"2000-04-30\";-2) =DATE(2000; 2;29)", Value(true)); // TODO 2000 was a leap year, so the end of February is the 29th 0374 CHECK_EVAL("EDATE(\"2000-04-05\";24 )=DATE(2002;04;05)", Value(true)); // EDATE isn't limited to 12 months 0375 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETEDATE(\"2006-01-01\";0) =DATE(2006;01;01)", Value(true)); // alternate function name 0376 } 0377 0378 void TestDatetimeFunctions::testEOMONTH() 0379 { 0380 // 0381 CHECK_EVAL("EOMONTH(\"2006-01-01\";0) =DATE(2006;01;31)", Value(true)); // If zero, unchanged V just returns 0382 // end of that date's month. (January in this case) 0383 CHECK_EVAL("EOMONTH(DATE(2006;01;01);0)=DATE(2006;01;31)", Value(true)); // You can pass strings or serial numbers to EOMONTH 0384 CHECK_EVAL("EOMONTH(\"2006-01-01\";2) =DATE(2006;03;31)", Value(true)); // End of month of March is March 31. 0385 CHECK_EVAL("EOMONTH(\"2006-01-01\";-2) =DATE(2005;11;30)", Value(true)); // Nov. 30 is the last day of November 0386 CHECK_EVAL("EOMONTH(\"2006-03-31\";-1) =DATE(2006;02;28)", Value(true)); // 2006 is not a leap year. Last day of February is Feb. 28. 0387 CHECK_EVAL("EOMONTH(\"2000-04-30\";-2) =DATE(2000;02;29)", Value(true)); // 2000 was a leap year, so the end of February is the 29th 0388 CHECK_EVAL("EOMONTH(\"2000-04-05\";24) =DATE(2002;04;30)", Value(true)); // Not limited to 12 months, and this tests April 0389 CHECK_EVAL("EOMONTH(\"2006-01-05\";04) =DATE(2002;05;31)", Value(false)); // End of May is May 31 0390 CHECK_EVAL("EOMONTH(\"2006-01-05\";05) =DATE(2002;06;30)", Value(false)); // June 30 0391 CHECK_EVAL("EOMONTH(\"2006-01-05\";06) =DATE(2002;07;31)", Value(false)); // July 31 0392 CHECK_EVAL("EOMONTH(\"2006-01-05\";07) =DATE(2002;08;31)", Value(false)); // August 31 0393 CHECK_EVAL("EOMONTH(\"2006-01-05\";08) =DATE(2002;09;30)", Value(false)); // Sep 30 0394 CHECK_EVAL("EOMONTH(\"2006-01-05\";09) =DATE(2002;10;31)", Value(false)); // Oct 31 0395 CHECK_EVAL("EOMONTH(\"2006-01-05\";10) =DATE(2002;11;30)", Value(false)); // Nov. 30 0396 CHECK_EVAL("EOMONTH(\"2006-01-05\";11) =DATE(2002;12;31)", Value(false)); // Dec. 31 0397 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETEOMONTH(\"2006-01-01\";0) =DATE(2006;01;31)", Value(true)); // alternate function name 0398 } 0399 0400 void TestDatetimeFunctions::testHOUR() 0401 { 0402 // Hacky way to test for 12h clock 0403 KLocale locale; 0404 bool twelveHourClock = locale.timeFormat().contains("%I"); 0405 0406 CHECK_EVAL("HOUR(5/24)", Value(5)); // 5/24ths of a day is 5 hours, aka 5AM. 0407 CHECK_EVAL("HOUR(5/24-1/(24*60*60))", Value(4)); // A second before 5AM, it's 4AM. 0408 // TimeParam accepts text 0409 CHECK_EVAL("HOUR(\"9:00\")", Value(9)); 0410 CHECK_EVAL("HOUR(\"09:00\")", Value(9)); 0411 CHECK_EVAL("HOUR(\"11:00 PM\")", Value(23)); 0412 CHECK_EVAL("HOUR(\"11:00 AM\")", Value(11)); 0413 0414 // These are locale dependent 0415 if (twelveHourClock) { 0416 CHECK_FAIL("HOUR(\"14:00\")", Value(14), "12h clock, hour must be <= 12"); 0417 CHECK_FAIL("HOUR(\"23:00\")", Value(23), "12h clock, hour must be <= 12"); 0418 } else { 0419 CHECK_EVAL("HOUR(\"14:00\")", Value(14)); 0420 CHECK_EVAL("HOUR(\"23:00\")", Value(23)); 0421 } 0422 0423 } 0424 0425 void TestDatetimeFunctions::testISOWEEKNUM() 0426 { 0427 // ODF-tests 0428 CHECK_EVAL("ISOWEEKNUM(DATE(1995;1;1);1)", Value(1)); // January 1, 1995 was a Sunday 0429 CHECK_EVAL("ISOWEEKNUM(DATE(1995;1;1);2)", Value(52)); // January 1, 1995 was a Sunday, so if Monday is the beginning of the week, 0430 // then it's week 52 of the previous year 0431 CHECK_EVAL("ISOWEEKNUM(DATE(1995;1;1))", Value(52)); // Default is Monday is beginning of week (per ISO) 0432 CHECK_EVAL("ISOWEEKNUM(DATE(2000;5;21))", Value(20)); // ref OOo-2.2.0 0433 CHECK_EVAL("ISOWEEKNUM(DATE(2000;5;21);1)", Value(21)); // ref OOo-2.2.0 0434 CHECK_EVAL("ISOWEEKNUM(DATE(2000;5;21);2)", Value(20)); // ref OOo-2.2.0 0435 CHECK_EVAL("ISOWEEKNUM(DATE(2005;1;1))", Value(53)); // ref OOo-2.2.0 0436 CHECK_EVAL("ISOWEEKNUM(DATE(2005;1;2))", Value(53)); // ref OOo-2.2.0 0437 CHECK_EVAL("ISOWEEKNUM(DATE(2006;1;1))", Value(52)); // ref OOo-2.2.0 0438 0439 // method 2 - week begins on sunday 0440 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;01);2)", Value(52)); // January 1, 1995 was a Sunday 0441 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;02);2)", Value(1)); // 0442 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;03);2)", Value(1)); // 0443 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;04);2)", Value(1)); // 0444 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;05);2)", Value(1)); // 0445 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;06);2)", Value(1)); // 0446 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;07);2)", Value(1)); // 0447 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;08);2)", Value(1)); // 0448 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;09);2)", Value(2)); // 0449 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;10);2)", Value(2)); // 0450 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;11);2)", Value(2)); // 0451 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;12);2)", Value(2)); // 0452 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;13);2)", Value(2)); // 0453 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;14);2)", Value(2)); // 0454 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;15);2)", Value(2)); // 0455 0456 // method 1 - week begins on monday 0457 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;01);1)", Value(1)); // January 1, 1995 was a Sunday 0458 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;02);1)", Value(1)); // 0459 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;03);1)", Value(1)); // 0460 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;04);1)", Value(1)); // 0461 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;05);1)", Value(1)); // 0462 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;06);1)", Value(1)); // 0463 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;07);1)", Value(1)); // 0464 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;08);1)", Value(2)); // 0465 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;09);1)", Value(2)); // 0466 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;10);1)", Value(2)); // 0467 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;11);1)", Value(2)); // 0468 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;12);1)", Value(2)); // 0469 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;13);1)", Value(2)); // 0470 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;14);1)", Value(2)); // 0471 CHECK_EVAL("ISOWEEKNUM(DATE(1995;01;15);1)", Value(3)); // 0472 } 0473 0474 void TestDatetimeFunctions::testMINUTE() 0475 { 0476 // 0477 CHECK_EVAL("MINUTE(1/(24*60))", Value(1)); // 1 minute is 1/(24*60) of a day. 0478 CHECK_EVAL("MINUTE(TODAY()+1/(24*60))", Value(1)); // If you start with today, and add a minute, you get a minute. 0479 CHECK_EVAL("MINUTE(1/24)", Value(0)); // At the beginning of the hour, we have 0 minutes. 0480 } 0481 0482 void TestDatetimeFunctions::testMONTH() 0483 { 0484 // 0485 CHECK_EVAL("MONTH(DATE(2006;5;21))", Value(5)); // Month extraction from DATE() value 0486 } 0487 0488 void TestDatetimeFunctions::testMONTHS() 0489 { 0490 CHECK_EVAL("MONTHS(\"2002-01-18\"; \"2002-02-26\"; 0)", Value(1)); 0491 CHECK_EVAL("MONTHS(\"2002-01-19\"; \"2002-02-26\"; 1)", Value(0)); 0492 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETDIFFMONTHS(\"2002-01-18\"; \"2002-02-26\"; 0)", Value(1)); 0493 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETDIFFMONTHS(\"2002-01-19\"; \"2002-02-26\"; 1)", Value(0)); 0494 } 0495 0496 void TestDatetimeFunctions::testNOW() 0497 { 0498 // 0499 CHECK_EVAL("NOW()>DATE(2006;1;3)", Value(true)); // NOW constantly changes, but we know it's beyond this date. 0500 CHECK_EVAL("INT(NOW())=TODAY()", Value(true)); 0501 } 0502 0503 void TestDatetimeFunctions::testSECOND() 0504 { 0505 // 0506 CHECK_EVAL("SECOND(1/(24*60*60))", Value(1)); // This is one second into today. 0507 CHECK_EVAL("SECOND(1/(24*60*60*2))", Value(1)); // Rounds. 0508 CHECK_EVAL("SECOND(1/(24*60*60*4))", Value(0)); // Rounds. 0509 } 0510 0511 void TestDatetimeFunctions::testTIME() 0512 { 0513 // 0514 CHECK_EVAL("TIME(0;0;0)", Value(0)); // All zero arguments becomes midnight, 12:00:00 AM. 0515 CHECK_EVAL("TIME(23;59;59)*60*60*24", Value(86399)); // This is 11:59:59 PM. 0516 CHECK_EVAL("TIME(11;125;144)*60*60*24", Value(47244)); // Seconds and minutes roll over transitively; this is 1:07:24 PM. 0517 CHECK_EVAL("TIME(11;0; -117)*60*60*24", Value(39483)); // Negative seconds roll minutes backwards, 10:58:03 AM 0518 CHECK_EVAL("TIME(11;-117;0)*60*60*24", Value(32580)); // Negative minutes roll hours backwards, 9:03:00 AM 0519 0520 CHECK_EVAL("TIME(11;-125;-144)*60*60*24", Value(31956)); // Negative seconds and minutes roll backwards transitively, 8:52:36 AM 0521 // WARNING specs says -31956, but calc and kspread calculate 31956 0522 } 0523 0524 void TestDatetimeFunctions::testTIMEVALUE() 0525 { 0526 // Hacky way to test for 12h clock 0527 KLocale locale; 0528 bool twelveHourClock = locale.timeFormat().contains("%I"); 0529 0530 CHECK_EVAL("TIMEVALUE(\"06:05\") =TIME(6;5;0)", Value(true)); 0531 0532 // When 12h clock, AM/PM is mandatory 0533 if (twelveHourClock) { 0534 CHECK_FAIL("TIMEVALUE(\"06:05:07\")=TIME(6;5;7)", Value(true), "12h clock, AM/PM is mandatory"); 0535 CHECK_EVAL("TIMEVALUE(\"06:05:07 am\")=TIME(6;5;7)", Value(true)); 0536 } else { 0537 CHECK_EVAL("TIMEVALUE(\"06:05:07\")=TIME(6;5;7)", Value(true)); 0538 } 0539 } 0540 0541 void TestDatetimeFunctions::testTODAY() 0542 { 0543 // 0544 CHECK_EVAL("TODAY()>DATE(2006;1;3)", Value(true)); // Every date TODAY() changes, but we know it's beyond this date. 0545 CHECK_EVAL("INT(TODAY())=TODAY()", Value(true)); 0546 } 0547 0548 void TestDatetimeFunctions::testWEEKDAY() 0549 { 0550 // | type 1 | type 2 | type 3 0551 // ---+--------+---------+-------- 0552 // 01 | SU | MO | TU 0553 // 02 | MO | TU | WE 0554 // 03 | TU | WE | TH 0555 // 04 | WE | TH | FR 0556 // 05 | TH | FR | SA 0557 // 06 | FR | SA | SU 0558 // 07 | SA | SU | MO 0559 0560 CHECK_EVAL("WEEKDAY(DATE(2006;05;21))", Value(1)); // Year-month-date format 0561 CHECK_EVAL("WEEKDAY(DATE(2005;01;01))", Value(7)); // Saturday 0562 CHECK_EVAL("WEEKDAY(DATE(2005;01;01);1)", Value(7)); // Saturday 0563 CHECK_EVAL("WEEKDAY(DATE(2005;01;01);2)", Value(6)); // Saturday 0564 CHECK_EVAL("WEEKDAY(DATE(2005;01;01);3)", Value(5)); // Saturday 0565 } 0566 0567 void TestDatetimeFunctions::testYEAR() 0568 { 0569 CHECK_EVAL("YEAR(DATE(1904;1;1))", Value(1904)); 0570 CHECK_EVAL("YEAR(DATE(2004;1;1))", Value(2004)); 0571 } 0572 0573 void TestDatetimeFunctions::testYEARS() 0574 { 0575 CHECK_EVAL("YEARS(\"2001-02-19\"; \"2002-02-26\"; 0)", Value(1)); 0576 CHECK_EVAL("YEARS(\"2002-02-19\"; \"2002-02-26\"; 1)", Value(0)); 0577 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETDIFFYEARS(\"2001-02-19\";\"2002-02-26\";0)", Value(1)); 0578 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETDIFFYEARS(\"2002-02-19\";\"2002-02-26\";1)", Value(0)); 0579 } 0580 0581 void TestDatetimeFunctions::testWEEKS() 0582 { 0583 CHECK_EVAL("WEEKS(\"2002-02-18\"; \"2002-02-26\"; 0)", Value(1)); 0584 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETDIFFWEEKS(\"2002-02-18\"; \"2002-02-26\"; 0)", Value(1)); 0585 } 0586 0587 QTEST_MAIN(TestDatetimeFunctions)