From 3739de6df80d0b20310c3c9d0fcab133fc734b0e Mon Sep 17 00:00:00 2001
From: James Ahlborn <jtahlborn@yahoo.com>
Date: Thu, 26 Jul 2018 20:22:13 +0000
Subject: test TimeSerial; implement DateSerial

git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@1187 f203690c-595d-4dc9-a70b-905162fa7fd2
---
 .../jackcess/impl/expr/DefaultDateFunctions.java   | 33 ++++++++++++++++++++++
 .../jackcess/impl/expr/DefaultFunctionsTest.java   |  9 ++++++
 2 files changed, 42 insertions(+)

diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java
index 7bfb256..ac5313c 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java
@@ -74,6 +74,31 @@ public class DefaultDateFunctions
     }
   });
 
+  public static final Function DATESERIAL = registerFunc(new Func3("DateSerial") {
+    @Override
+    protected Value eval3(EvalContext ctx, Value param1, Value param2, Value param3) {
+      int year = param1.getAsLongInt();
+      int month = param2.getAsLongInt();
+      int day = param3.getAsLongInt();
+
+      // "default" two digit year handling
+      if(year < 100) {
+        year += ((year <= 29) ? 2000 : 1900);
+      }
+
+      DateFormat fmt = BuiltinOperators.getDateFormatForType(ctx, Value.Type.DATE);
+      Calendar cal = fmt.getCalendar();
+      cal.clear();
+
+      cal.set(Calendar.YEAR, year);
+      // convert to 0 based value
+      cal.set(Calendar.MONTH, month - 1);
+      cal.set(Calendar.DAY_OF_MONTH, day);
+
+      return BuiltinOperators.toValue(Value.Type.DATE, cal.getTime(), fmt);
+    }
+  });
+
   public static final Function NOW = registerFunc(new Func0("Now") {
     @Override
     protected Value eval0(EvalContext ctx) {
@@ -122,6 +147,14 @@ public class DefaultDateFunctions
 
       long totalSeconds = (hours * SECONDS_PER_HOUR) +
         (minutes * SECONDS_PER_MINUTE) + seconds;
+      if(totalSeconds < 0L) {
+        do {
+          totalSeconds += SECONDS_PER_DAY;
+        } while(totalSeconds < 0L);
+      } else if(totalSeconds > SECONDS_PER_DAY) {
+        totalSeconds %= SECONDS_PER_DAY;
+      }
+
       DateFormat fmt = BuiltinOperators.getDateFormatForType(ctx, Value.Type.TIME);
       double dd = totalSeconds / DSECONDS_PER_DAY;
       return BuiltinOperators.toValue(Value.Type.TIME, dd, fmt);
diff --git a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
index 8248c08..ca1a67c 100644
--- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
+++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
@@ -237,6 +237,15 @@ public class DefaultFunctionsTest extends TestCase
 
     assertEquals("3:57:34 AM", eval("=CStr(TimeSerial(3,57,34))"));
     assertEquals("3:57:34 PM", eval("=CStr(TimeSerial(15,57,34))"));
+    assertEquals("5:45:00 AM", eval("=CStr(TimeSerial(6,-15,0))"));
+    assertEquals("12:00:00 AM", eval("=CStr(TimeSerial(0,0,0))"));
+    assertEquals("2:00:00 PM", eval("=CStr(TimeSerial(-10,0,0))"));
+    assertEquals("6:00:00 AM", eval("=CStr(TimeSerial(30,0,0))"));
+
+    assertEquals("2/12/1969", eval("=CStr(DateSerial(69,2,12))"));
+    assertEquals("2/12/2010", eval("=CStr(DateSerial(10,2,12))"));
+    assertEquals("7/12/2013", eval("=CStr(DateSerial(2014,-5,12))"));
+    assertEquals("8/7/2013", eval("=CStr(DateSerial(2014,-5,38))"));
   }
 
   public void testFinancialFuncs() throws Exception
-- 
cgit v1.2.3