rework string conversion to date; add support for dates with implicit years; implement DatePart function

git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@1210 f203690c-595d-4dc9-a70b-905162fa7fd2

15 files changed, 417 insertions, 138 deletions

diff --git a/src/main/java/com/healthmarketscience/jackcess/expr/TemporalConfig.java b/src/main/java/com/healthmarketscience/jackcess/expr/TemporalConfig.java
index d7061ca..b7de667 100644
--- a/src/main/java/com/healthmarketscience/jackcess/expr/TemporalConfig.java
+++ b/src/main/java/com/healthmarketscience/jackcess/expr/TemporalConfig.java
@@ -31,12 +31,14 @@ import java.util.Locale;
 public class TemporalConfig
 {
   public static final String US_DATE_FORMAT = "M/d/yyyy";
+  public static final String US_DATE_IMPLICIT_YEAR_FORMAT = "M/d";
   public static final String US_TIME_FORMAT_12 = "h:mm:ss a";
   public static final String US_TIME_FORMAT_24 = "H:mm:ss";
 
   /** default implementation which is configured for the US locale */
   public static final TemporalConfig US_TEMPORAL_CONFIG = new TemporalConfig(
-      US_DATE_FORMAT, US_TIME_FORMAT_12, US_TIME_FORMAT_24, '/', ':', Locale.US);
+      US_DATE_FORMAT, US_DATE_IMPLICIT_YEAR_FORMAT,
+      US_TIME_FORMAT_12, US_TIME_FORMAT_24, '/', ':', Locale.US);
 
   public enum Type {
     DATE, TIME, DATE_TIME, TIME_12, TIME_24, DATE_TIME_12, DATE_TIME_24;
@@ -74,9 +76,37 @@ public class TemporalConfig
         throw new RuntimeException("invalid type " + this);
       }
     }
+
+    public boolean includesDate() {
+      switch(this) {
+      case DATE:
+      case DATE_TIME:
+      case DATE_TIME_12:
+      case DATE_TIME_24:
+        return true;
+      default:
+        return false;
+      }
+    }
+
+    public boolean includesTime() {
+      return (this != DATE);
+    }
+
+    public boolean isTimeOnly() {
+      switch(this) {
+      case TIME:
+      case TIME_12:
+      case TIME_24:
+        return true;
+      default:
+        return false;
+      }
+    }
   }
 
   private final String _dateFormat;
+  private final String _dateImplicitYearFormat;
   private final String _timeFormat12;
   private final String _timeFormat24;
   private final char _dateSeparator;
@@ -92,6 +122,7 @@ public class TemporalConfig
    * <time>".
    *
    * @param dateFormat the date (no time) format
+   * @param dateImplicitYearFormat the date (no time) with no year format
    * @param timeFormat12 the 12 hour time format
    * @param timeFormat24 the 24 hour time format
    * @param dateSeparator the primary separator used to separate elements in
@@ -103,11 +134,12 @@ public class TemporalConfig
    *                      string.  This value should differ from the
    *                      dateSeparator.
    */
-  public TemporalConfig(String dateFormat, String timeFormat12,
-                        String timeFormat24, char dateSeparator,
-                        char timeSeparator, Locale locale)
+  public TemporalConfig(String dateFormat, String dateImplicitYearFormat,
+                        String timeFormat12, String timeFormat24,
+                        char dateSeparator, char timeSeparator, Locale locale)
   {
     _dateFormat = dateFormat;
+    _dateImplicitYearFormat = dateImplicitYearFormat;
     _timeFormat12 = timeFormat12;
     _timeFormat24 = timeFormat24;
     _dateSeparator = dateSeparator;
@@ -178,7 +210,27 @@ public class TemporalConfig
     }
   }
 
+  public String getImplicitYearDateTimeFormat(Type type) {
+    switch(type) {
+    case DATE:
+      return _dateImplicitYearFormat;
+    case DATE_TIME:
+      return toDateTimeFormat(_dateImplicitYearFormat, getDefaultTimeFormat());
+    case DATE_TIME_12:
+      return toDateTimeFormat(_dateImplicitYearFormat, getTimeFormat12());
+    case DATE_TIME_24:
+      return toDateTimeFormat(_dateImplicitYearFormat, getTimeFormat24());
+    default:
+      throw new IllegalArgumentException(
+          "the given format does not include a date " + type);
+    }
+  }
+
   public DateFormatSymbols getDateFormatSymbols() {
     return _symbols;
   }
+
+  private static String toDateTimeFormat(String dateFormat, String timeFormat) {
+    return dateFormat + " " + timeFormat;
+  }
 }
diff --git a/src/main/java/com/healthmarketscience/jackcess/expr/Value.java b/src/main/java/com/healthmarketscience/jackcess/expr/Value.java
index f9bbf96..e9508fa 100644
--- a/src/main/java/com/healthmarketscience/jackcess/expr/Value.java
+++ b/src/main/java/com/healthmarketscience/jackcess/expr/Value.java
@@ -98,6 +98,14 @@ public interface Value
   public Date getAsDateTime(LocaleContext ctx);
 
   /**
+   * Since date/time values have different types, it may be more convenient to
+   * get the date/time primitive value with the appropriate type information.
+   *
+   * @return this value converted to a date/time value
+   */
+  public Value getAsDateTimeValue(LocaleContext ctx);
+
+  /**
    * @return this primitive value converted (rounded) to an int
    */
   public Integer getAsLongInt(LocaleContext ctx);
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseDelayedValue.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseDelayedValue.java
index 6805d07..d527c69 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseDelayedValue.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseDelayedValue.java
@@ -64,6 +64,10 @@ public abstract class BaseDelayedValue implements Value
     return getDelegate().getAsDateTime(ctx);
   }
 
+  public Value getAsDateTimeValue(LocaleContext ctx) {
+    return getDelegate().getAsDateTimeValue(ctx);
+  }
+
   public Integer getAsLongInt(LocaleContext ctx) {
     return getDelegate().getAsLongInt(ctx);
   }
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseNumericValue.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseNumericValue.java
index cdf686c..299cd2a 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseNumericValue.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseNumericValue.java
@@ -16,11 +16,9 @@ limitations under the License.
 
 package com.healthmarketscience.jackcess.impl.expr;
 
-import java.text.SimpleDateFormat;
-import java.util.Date;
 
 import com.healthmarketscience.jackcess.expr.LocaleContext;
-import com.healthmarketscience.jackcess.impl.ColumnImpl;
+import com.healthmarketscience.jackcess.expr.Value;
 
 /**
  *
@@ -44,9 +42,13 @@ public abstract class BaseNumericValue extends BaseValue
   }
 
   @Override
-  public Date getAsDateTime(LocaleContext ctx) {
-    double d = getNumber().doubleValue();
-    return new Date(ColumnImpl.fromDateDouble(d, ctx.getCalendar()));
+  public Value getAsDateTimeValue(LocaleContext ctx) {
+    Value dateValue = DefaultDateFunctions.numberToDateValue(
+        ctx, getNumber().doubleValue());
+    if(dateValue == null) {
+      throw invalidConversion(Value.Type.DATE_TIME);
+    }
+    return dateValue;
   }
 
   protected abstract Number getNumber();
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseValue.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseValue.java
index d83af09..b444218 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseValue.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BaseValue.java
@@ -35,32 +35,36 @@ public abstract class BaseValue implements Value
   }
 
   public boolean getAsBoolean(LocaleContext ctx) {
-    throw invalidConversion(Value.Type.LONG);
+    throw invalidConversion(Type.LONG);
   }
 
   public String getAsString(LocaleContext ctx) {
-    throw invalidConversion(Value.Type.STRING);
+    throw invalidConversion(Type.STRING);
   }
 
   public Date getAsDateTime(LocaleContext ctx) {
-    throw invalidConversion(Value.Type.DATE_TIME);
+    return (Date)getAsDateTimeValue(ctx).get();
+  }
+
+  public Value getAsDateTimeValue(LocaleContext ctx) {
+    throw invalidConversion(Type.DATE_TIME);
   }
 
   public Integer getAsLongInt(LocaleContext ctx) {
-    throw invalidConversion(Value.Type.LONG);
+    throw invalidConversion(Type.LONG);
   }
 
   public Double getAsDouble(LocaleContext ctx) {
-    throw invalidConversion(Value.Type.DOUBLE);
+    throw invalidConversion(Type.DOUBLE);
   }
 
   public BigDecimal getAsBigDecimal(LocaleContext ctx) {
-    throw invalidConversion(Value.Type.BIG_DEC);
+    throw invalidConversion(Type.BIG_DEC);
   }
 
-  private EvalException invalidConversion(Value.Type newType) {
+  protected EvalException invalidConversion(Type newType) {
     return new EvalException(
-        getType() + " value cannot be converted to " + newType);
+        this + " cannot be converted to " + newType);
   }
 
   protected Integer roundToLongInt(LocaleContext ctx) {
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java
index f53a6be..be893c3 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/BuiltinOperators.java
@@ -655,7 +655,7 @@ public class BuiltinOperators
                     Value.Type.BIG_DEC : Value.Type.DOUBLE);
       }
       return prefType;
-    } catch(NumberFormatException ignored) {
+    } catch(EvalException ignored) {
       // not a number
     }
 
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DateTimeValue.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DateTimeValue.java
index 0f6937f..e2de36d 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DateTimeValue.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DateTimeValue.java
@@ -21,6 +21,7 @@ import java.util.Date;
 
 import com.healthmarketscience.jackcess.impl.ColumnImpl;
 import com.healthmarketscience.jackcess.expr.LocaleContext;
+import com.healthmarketscience.jackcess.expr.Value;
 
 /**
  *
@@ -68,6 +69,11 @@ public class DateTimeValue extends BaseValue
   }
 
   @Override
+  public Value getAsDateTimeValue(LocaleContext ctx) {
+    return this;
+  }
+
+  @Override
   public Integer getAsLongInt(LocaleContext ctx) {
     return roundToLongInt(ctx);
   }
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 f5061d4..4039192 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java
@@ -50,6 +50,18 @@ public class DefaultDateFunctions
   private static final long SECONDS_PER_HOUR = 60L * 60L;
   private static final long SECONDS_PER_MINUTE = 60L;
 
+  private static final String INTV_YEAR = "yyyy";
+  private static final String INTV_QUARTER = "q";
+  private static final String INTV_MONTH = "m";
+  private static final String INTV_DAY_OF_YEAR = "y";
+  private static final String INTV_DAY = "d";
+  private static final String INTV_WEEKDAY = "w";
+  private static final String INTV_WEEK = "ww";
+  private static final String INTV_HOUR = "h";
+  private static final String INTV_MINUTE = "n";
+  private static final String INTV_SECOND = "s";
+
+
   private DefaultDateFunctions() {}
 
   static void init() {
@@ -67,7 +79,7 @@ public class DefaultDateFunctions
   public static final Function DATEVALUE = registerFunc(new Func1NullIsNull("DateValue") {
     @Override
     protected Value eval1(EvalContext ctx, Value param1) {
-      Value dv = nonNullToDateValue(ctx, param1);
+      Value dv = param1.getAsDateTimeValue(ctx);
       if(dv.getType() == Value.Type.DATE) {
         return dv;
       }
@@ -100,6 +112,51 @@ public class DefaultDateFunctions
     }
   });
 
+  public static final Function DATEPART = registerFunc(new FuncVar("DatePart", 2, 4) {
+    @Override
+    protected Value evalVar(EvalContext ctx, Value[] params) {
+      Value param2 = params[1];
+      if(param2.isNull()) {
+        return ValueSupport.NULL_VAL;
+      }
+
+      int firstDay = getFirstDayParam(ctx, params, 2);
+      int firstWeekType = getFirstWeekTypeParam(ctx, params, 3);
+
+      String intv = params[0].getAsString(ctx).trim();
+      int result = -1;
+      if(intv.equalsIgnoreCase(INTV_YEAR)) {
+        result = nonNullToCalendarField(ctx, param2, Calendar.YEAR);
+      } else if(intv.equalsIgnoreCase(INTV_QUARTER)) {
+        // month in is 0 based
+        int month = nonNullToCalendarField(ctx, param2, Calendar.MONTH);
+        result = (month / 3) + 1;
+      } else if(intv.equalsIgnoreCase(INTV_MONTH)) {
+        // convert from 0 based to 1 based value
+        result = nonNullToCalendarField(ctx, param2, Calendar.MONTH) + 1;
+      } else if(intv.equalsIgnoreCase(INTV_DAY_OF_YEAR)) {
+        result = nonNullToCalendarField(ctx, param2, Calendar.DAY_OF_YEAR);
+      } else if(intv.equalsIgnoreCase(INTV_DAY)) {
+        result = nonNullToCalendarField(ctx, param2, Calendar.DAY_OF_MONTH);
+      } else if(intv.equalsIgnoreCase(INTV_WEEKDAY)) {
+        int dayOfWeek = nonNullToCalendarField(ctx, param2, Calendar.DAY_OF_WEEK);
+        result = dayOfWeekToWeekDay(dayOfWeek, firstDay);
+      } else if(intv.equalsIgnoreCase(INTV_WEEK)) {
+        result = weekOfYear(ctx, param2, firstDay, firstWeekType);
+      } else if(intv.equalsIgnoreCase(INTV_HOUR)) {
+        result = nonNullToCalendarField(ctx, param2, Calendar.HOUR_OF_DAY);
+      } else if(intv.equalsIgnoreCase(INTV_MINUTE)) {
+        result = nonNullToCalendarField(ctx, param2, Calendar.MINUTE);
+      } else if(intv.equalsIgnoreCase(INTV_SECOND)) {
+        result = nonNullToCalendarField(ctx, param2, Calendar.SECOND);
+      } else {
+        throw new EvalException("Invalid interval " + intv);
+      }
+
+      return ValueSupport.toValue(result);
+    }
+  });
+
   public static final Function NOW = registerFunc(new Func0("Now") {
     @Override
     protected Value eval0(EvalContext ctx) {
@@ -118,7 +175,7 @@ public class DefaultDateFunctions
   public static final Function TIMEVALUE = registerFunc(new Func1NullIsNull("TimeValue") {
     @Override
     protected Value eval1(EvalContext ctx, Value param1) {
-      Value dv = nonNullToDateValue(ctx, param1);
+      Value dv = param1.getAsDateTimeValue(ctx);
       if(dv.getType() == Value.Type.TIME) {
         return dv;
       }
@@ -202,8 +259,8 @@ public class DefaultDateFunctions
     @Override
     protected Value evalVar(EvalContext ctx, Value[] params) {
       Value param1 = params[0];
-      if(param1 == null) {
-        return null;
+      if(param1.isNull()) {
+        return ValueSupport.NULL_VAL;
       }
       // convert from 1 based to 0 based value
       int month = param1.getAsLongInt(ctx) - 1;
@@ -230,8 +287,8 @@ public class DefaultDateFunctions
     @Override
     protected Value evalVar(EvalContext ctx, Value[] params) {
       Value param1 = params[0];
-      if(param1 == null) {
-        return null;
+      if(param1.isNull()) {
+        return ValueSupport.NULL_VAL;
       }
       int dayOfWeek = nonNullToCalendarField(ctx, param1, Calendar.DAY_OF_WEEK);
 
@@ -245,8 +302,8 @@ public class DefaultDateFunctions
     @Override
     protected Value evalVar(EvalContext ctx, Value[] params) {
       Value param1 = params[0];
-      if(param1 == null) {
-        return null;
+      if(param1.isNull()) {
+        return ValueSupport.NULL_VAL;
       }
       int weekday = param1.getAsLongInt(ctx);
 
@@ -270,59 +327,46 @@ public class DefaultDateFunctions
   }
 
   private static Calendar nonNullToCalendar(EvalContext ctx, Value param) {
-    Value origParam = param;
-    param = nonNullToDateValue(ctx, param);
-    if(param == null) {
-      // not a date/time
-      throw new EvalException("Invalid date/time expression '" +
-                              origParam + "'");
-    }
-
     Calendar cal = ctx.getCalendar();
     cal.setTime(param.getAsDateTime(ctx));
     return cal;
   }
 
-  static Value nonNullToDateValue(EvalContext ctx, Value param) {
-    Value.Type type = param.getType();
-    if(type.isTemporal()) {
-      return param;
-    }
-
-    if(type == Value.Type.STRING) {
+  static Value stringToDateValue(LocaleContext ctx, String valStr) {
+    // see if we can coerce to date/time
+    TemporalConfig.Type valTempType = ExpressionTokenizer.determineDateType(
+        valStr, ctx);
 
-      // see if we can coerce to date/time or double
-      String valStr = param.getAsString(ctx);
-      TemporalConfig.Type valTempType = ExpressionTokenizer.determineDateType(
-          valStr, ctx);
+    if(valTempType != null) {
 
-      if(valTempType != null) {
+      DateFormat parseDf = ExpressionTokenizer.createParseDateTimeFormat(
+          valTempType, ctx);
 
-        try {
-          DateFormat parseDf = ExpressionTokenizer.createParseDateTimeFormat(
+      try {
+        Date dateVal = ExpressionTokenizer.parseComplete(parseDf, valStr);
+        return ValueSupport.toValue(valTempType.getValueType(), dateVal);
+      } catch(java.text.ParseException pe) {
+
+        if(valTempType.includesDate()) {
+          // the date may not include a year value, in which case it means
+          // to use the "current" year.  see if this is an implicit year date
+          parseDf = ExpressionTokenizer.createParseImplicitYearDateTimeFormat(
               valTempType, ctx);
-          Date dateVal = ExpressionTokenizer.parseComplete(parseDf, valStr);
-          return ValueSupport.toValue(valTempType.getValueType(), dateVal);
-        } catch(java.text.ParseException pe) {
-          // not a valid date string, not a date/time
-          return null;
+          try {
+            Date dateVal = ExpressionTokenizer.parseComplete(parseDf, valStr);
+            return ValueSupport.toValue(valTempType.getValueType(), dateVal);
+          } catch(java.text.ParseException pe2) {
+            // guess not, continue on to failure
+          }
         }
       }
-
-      // see if string can be coerced to number
-      try {
-        return numberToDateValue(ctx, param.getAsDouble(ctx));
-      } catch(NumberFormatException ignored) {
-        // not a number, not a date/time
-        return null;
-      }
     }
 
-    // must be a number
-    return numberToDateValue(ctx, param.getAsDouble(ctx));
+    // not a valid date string, not a date/time
+    return null;
   }
 
-  private static Value numberToDateValue(EvalContext ctx, double dd) {
+  static Value numberToDateValue(LocaleContext ctx, double dd) {
     if((dd < MIN_DATE) || (dd > MAX_DATE)) {
       // outside valid date range
       return null;
@@ -345,7 +389,7 @@ public class DefaultDateFunctions
 
   private static double timeOnly(double dd) {
     // the fractional part of the date/time double is the time value.  discard
-    // the integral portion and convert to seconds
+    // the integral portion
     return new BigDecimal(dd).remainder(BigDecimal.ONE).doubleValue();
   }
 
@@ -367,15 +411,67 @@ public class DefaultDateFunctions
 
   private static int getFirstDayParam(
       LocaleContext ctx, Value[] params, int idx) {
-    // vbSunday (default)
-    int firstDay = 1;
+    // vbSunday (default) 1
+    // vbUseSystem 0
+    return getOptionalIntParam(ctx, params, idx, 1, 0);
+  }
+
+  private static int getFirstWeekTypeParam(
+      LocaleContext ctx, Value[] params, int idx) {
+    // vbFirstJan1 (default) 1
+    // vbUseSystem 0
+    return getOptionalIntParam(ctx, params, idx, 1, 0);
+  }
+
+  private static int getOptionalIntParam(
+      LocaleContext ctx, Value[] params, int idx, int defValue, int useDefValue) {
+    int val = defValue;
     if(params.length > idx) {
-      firstDay = params[idx].getAsLongInt(ctx);
-      if(firstDay == 0) {
-        // 0 == vbUseSystem, so we will use the default "sunday"
-        firstDay = 1;
+      val = params[idx].getAsLongInt(ctx);
+      if(val == useDefValue) {
+        val = defValue;
+      }
+    }
+    return val;
+  }
+
+  private static int weekOfYear(EvalContext ctx, Value param, int firstDay,
+                                int firstWeekType) {
+    Calendar cal = nonNullToCalendar(ctx, param);
+
+    // need to mess with some calendar settings, but they need to be restored
+    // when done because the Calendar instance may be shared
+    int origFirstDay = cal.getFirstDayOfWeek();
+    int origMinDays = cal.getMinimalDaysInFirstWeek();
+    try {
+
+      int minDays = 1;
+      switch(firstWeekType) {
+      case 1:
+        // vbUseSystem 0
+        // vbFirstJan1 1 (default)
+        break;
+      case 2:
+        // vbFirstFourDays 2
+        minDays = 4;
+        break;
+      case 3:
+        // vbFirstFullWeek 3
+        minDays = 7;
+        break;
+      default:
+        throw new EvalException("Invalid first week of year type " +
+                                firstWeekType);
       }
+
+      cal.setFirstDayOfWeek(firstDay);
+      cal.setMinimalDaysInFirstWeek(minDays);
+
+      return cal.get(Calendar.WEEK_OF_YEAR);
+
+    } finally {
+      cal.setFirstDayOfWeek(origFirstDay);
+      cal.setMinimalDaysInFirstWeek(origMinDays);
     }
-    return firstDay;
   }
 }
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java
index f794a62..331a47e 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java
@@ -163,7 +163,7 @@ public class DefaultFunctions
   public static final Function CDATE = registerFunc(new Func1("CDate") {
     @Override
     protected Value eval1(EvalContext ctx, Value param1) {
-      return DefaultDateFunctions.nonNullToDateValue(ctx, param1);
+      return param1.getAsDateTimeValue(ctx);
     }
   });
   static {
@@ -240,9 +240,21 @@ public class DefaultFunctions
   public static final Function ISDATE = registerFunc(new Func1("IsDate") {
     @Override
     protected Value eval1(EvalContext ctx, Value param1) {
-      return ValueSupport.toValue(
-          !param1.isNull() &&
-          (DefaultDateFunctions.nonNullToDateValue(ctx, param1) != null));
+      if(param1.getType().isTemporal()) {
+        return ValueSupport.TRUE_VAL;
+      }
+
+      // for the purposes of this method, a string literal should only
+      // return true if it is explicitly a date/time, not if it is just a
+      // number (even though casting a number string to a date/time works in
+      // general)
+      if((param1.getType() == Value.Type.STRING) &&
+         !stringIsNumeric(ctx, param1) &&
+         stringIsTemporal(ctx, param1)) {
+        return ValueSupport.TRUE_VAL;
+      }
+
+      return ValueSupport.FALSE_VAL;
     }
   });
 
@@ -253,13 +265,11 @@ public class DefaultFunctions
         return ValueSupport.TRUE_VAL;
       }
 
-      if(param1.getType() == Value.Type.STRING) {
-        try {
-          param1.getAsBigDecimal(ctx);
-          return ValueSupport.TRUE_VAL;
-        } catch(NumberFormatException ignored) {
-          // fall through to FALSE_VAL
-        }
+      // note, only a string can be considered numberic for this function,
+      // even though a date/time can be cast to a number in general
+      if((param1.getType() == Value.Type.STRING) &&
+         stringIsNumeric(ctx, param1)) {
+        return ValueSupport.TRUE_VAL;
       }
 
       return ValueSupport.FALSE_VAL;
@@ -338,7 +348,26 @@ public class DefaultFunctions
     }
   });
 
+  private static boolean stringIsNumeric(EvalContext ctx, Value param) {
+    try {
+      param.getAsBigDecimal(ctx);
+      return true;
+    } catch(EvalException ignored) {
+      // fall through to false
+    }
+    return false;
+  }
 
+  private static boolean stringIsTemporal(EvalContext ctx, Value param) {
+    try {
+      // see if we can coerce to date/time
+      param.getAsDateTimeValue(ctx);
+      return true;
+    } catch(EvalException ignored) {
+      // not a date/time
+    }
+    return false;
+  }
 
   // https://www.techonthenet.com/access/functions/
   // https://support.office.com/en-us/article/Access-Functions-by-category-b8b136c3-2716-4d39-94a2-658ce330ed83
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java
index 4473af4..2e8a04e 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java
@@ -130,7 +130,7 @@ public class DefaultNumberFunctions
     protected Value evalVar(EvalContext ctx, Value[] params) {
       Value param1 = params[0];
       if(param1.isNull()) {
-        return null;
+        return ValueSupport.NULL_VAL;
       }
       if(param1.getType().isIntegral()) {
         return param1;
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/ExpressionTokenizer.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/ExpressionTokenizer.java
index 3dfa361..c94a609 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/ExpressionTokenizer.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/ExpressionTokenizer.java
@@ -54,8 +54,10 @@ class ExpressionTokenizer
   private static final char EQUALS_CHAR = '=';
 
   // access times are based on this date (not the UTC base)
-  private static final String BASE_DATE = "12/30/1899";
-  private static final String BASE_DATE_FMT = "M/d/yyyy";
+  private static final String BASE_DATE_PREFIX = "1899/12/30 ";
+  private static final String BASE_DATE_FMT_PREFIX = "yyyy/M/d ";
+
+  private static final String IMPLICIT_YEAR_FMT_PREFIX = "yyyy ";
 
   private static final byte IS_OP_FLAG =     0x01;
   private static final byte IS_COMP_FLAG =   0x02;
@@ -350,47 +352,18 @@ class ExpressionTokenizer
   static DateFormat createParseDateTimeFormat(TemporalConfig.Type type,
                                               LocaleContext ctx)
   {
-    switch(type) {
-    case TIME:
-      return createParseTimeFormat(TemporalConfig.Type.DATE_TIME, ctx);
-    case TIME_12:
-      return createParseTimeFormat(TemporalConfig.Type.DATE_TIME_12, ctx);
-    case TIME_24:
-      return createParseTimeFormat(TemporalConfig.Type.DATE_TIME_24, ctx);
-    default:
-      // use normal formatter
+    if(type.isTimeOnly()) {
+      return new ParseTimeFormat(type, ctx);
     }
 
     TemporalConfig cfg = ctx.getTemporalConfig();
     return ctx.createDateFormat(cfg.getDateTimeFormat(type));
   }
 
-  private static DateFormat createParseTimeFormat(TemporalConfig.Type parseType,
-                                                  LocaleContext ctx)
+  static DateFormat createParseImplicitYearDateTimeFormat(
+      TemporalConfig.Type type, LocaleContext ctx)
   {
-    TemporalConfig cfg = ctx.getTemporalConfig();
-    // we need to use a special DateFormat impl which manipulates the parsed
-    // time-only value so it becomes the right Date value
-    String baseDate = getBaseDatePrefix(ctx);
-    DateFormat parseDf = ctx.createDateFormat(
-        cfg.getDateTimeFormat(parseType));
-    return new ParseTimeFormat(parseDf, baseDate);
-  }
-
-  private static String getBaseDatePrefix(LocaleContext ctx) {
-    String dateFmt = ctx.getTemporalConfig().getDateFormat();
-    String baseDate = BASE_DATE;
-    if(!BASE_DATE_FMT.equals(dateFmt)) {
-      try {
-        // need to reformat the base date to the relevant date format
-        DateFormat parseDf = ctx.createDateFormat(BASE_DATE_FMT);
-        DateFormat df = ctx.createDateFormat(dateFmt);
-        baseDate = df.format(parseComplete(parseDf, baseDate));
-      } catch(Exception e) {
-        throw new ParseException("Could not parse base date", e);
-      }
-    }
-    return baseDate + " ";
+    return new ParseImplicitYearFormat(type, ctx);
   }
 
   private static Token maybeParseNumberLiteral(char firstChar, ExprBuf buf) {
@@ -630,32 +603,37 @@ class ExpressionTokenizer
   }
 
   /**
-   * Special date/time format which will parse time-only strings "correctly"
-   * according to how access handles time-only values.
+   * Base DateFormat implementation for parsing date/time formats where
+   * additional information is added on to the format in order for it to be
+   * parsed correctly.
    */
-  private static final class ParseTimeFormat extends DateFormat
+  private static abstract class ParsePrefixFormat extends DateFormat
   {
     private static final long serialVersionUID = 0L;
 
     private final DateFormat _parseDelegate;
-    private final String _baseDate;
 
-    private ParseTimeFormat(DateFormat parseDelegate, String baseDate)
-    {
-      _parseDelegate = parseDelegate;
-      _baseDate = baseDate;
+    private ParsePrefixFormat(String formatPrefix, String formatStr,
+                              LocaleContext ctx) {
+      _parseDelegate = ctx.createDateFormat(formatPrefix + formatStr);
     }
 
     @Override
-    public StringBuffer format(Date date, StringBuffer toAppendTo, FieldPosition fieldPosition) {
+    public StringBuffer format(Date date, StringBuffer toAppendTo,
+                               FieldPosition fieldPosition) {
       throw new UnsupportedOperationException();
     }
 
     @Override
     public Date parse(String source, ParsePosition pos) {
-      // we parse as a full date/time in order to get the correct "base date"
-      // used by access
-      return _parseDelegate.parse(_baseDate + source, pos);
+      String prefix = getPrefix();
+
+      Date result = _parseDelegate.parse(prefix + source, pos);
+
+      // adjust index for original string
+      pos.setIndex(pos.getIndex() - prefix.length());
+
+      return result;
     }
 
     @Override
@@ -667,6 +645,55 @@ class ExpressionTokenizer
     public TimeZone getTimeZone() {
       return _parseDelegate.getTimeZone();
     }
+
+    protected abstract String getPrefix();
+  }
+
+  /**
+   * Special date/time format which will parse time-only strings "correctly"
+   * according to how access handles time-only values.
+   */
+  private static final class ParseTimeFormat extends ParsePrefixFormat
+  {
+    private static final long serialVersionUID = 0L;
+
+    private ParseTimeFormat(TemporalConfig.Type timeType, LocaleContext ctx) {
+      super(BASE_DATE_FMT_PREFIX,
+            ctx.getTemporalConfig().getDateTimeFormat(timeType), ctx);
+    }
+
+    @Override
+    protected String getPrefix() {
+      // we parse as a full date/time in order to get the correct "base date"
+      // used by access
+      return BASE_DATE_PREFIX;
+    }
+  }
+
+  /**
+   * Special date/time format which will parse dates with implicit (current)
+   * years.
+   */
+  private static final class ParseImplicitYearFormat extends ParsePrefixFormat
+  {
+    private static final long serialVersionUID = 0L;
+
+    private ParseImplicitYearFormat(TemporalConfig.Type type,
+                                    LocaleContext ctx) {
+      super(IMPLICIT_YEAR_FMT_PREFIX,
+            ctx.getTemporalConfig().getImplicitYearDateTimeFormat(type),
+            ctx);
+    }
+
+    @Override
+    protected String getPrefix() {
+      // need to get the current year
+      Calendar cal = getCalendar();
+      cal.setTimeInMillis(System.currentTimeMillis());
+      int year = cal.get(Calendar.YEAR);
+      // return a value matching IMPLICIT_YEAR_FMT_PREFIX
+      return year + " ";
+    }
   }
 
 }
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/FunctionSupport.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/FunctionSupport.java
index 91d98a1..79e4609 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/FunctionSupport.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/FunctionSupport.java
@@ -254,5 +254,4 @@ public class FunctionSupport
     }
     return defValue;
   }
-
 }
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/StringValue.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/StringValue.java
index 9988477..12a5efb 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/StringValue.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/StringValue.java
@@ -21,7 +21,9 @@ import java.math.BigInteger;
 import java.text.DecimalFormatSymbols;
 import java.util.regex.Pattern;
 
+import com.healthmarketscience.jackcess.expr.EvalException;
 import com.healthmarketscience.jackcess.expr.LocaleContext;
+import com.healthmarketscience.jackcess.expr.Value;
 import org.apache.commons.lang.StringUtils;
 
 /**
@@ -81,6 +83,30 @@ public class StringValue extends BaseValue
     return getNumber(ctx);
   }
 
+  @Override
+  public Value getAsDateTimeValue(LocaleContext ctx) {
+    Value dateValue = DefaultDateFunctions.stringToDateValue(ctx, _val);
+
+    if(dateValue == null) {
+      // see if string can be coerced to number and then to value date (note,
+      // numberToDateValue may return null for out of range numbers)
+      try {
+        dateValue = DefaultDateFunctions.numberToDateValue(
+            ctx, getNumber(ctx).doubleValue());
+      } catch(EvalException ignored) {
+        // not a number, not a date/time
+      }
+
+      if(dateValue == null) {
+        throw invalidConversion(Type.DATE_TIME);
+      }
+    }
+
+    // TODO, for now, we can't cache the date value becuase it could be an
+    // "implicit" date which would need to be re-calculated on each call
+    return dateValue;
+  }
+
   protected BigDecimal getNumber(LocaleContext ctx) {
     if(_num instanceof BigDecimal) {
       return (BigDecimal)_num;
@@ -114,7 +140,7 @@ public class StringValue extends BaseValue
       }
       _num = NOT_A_NUMBER;
     }
-    throw new NumberFormatException("Invalid number '" + _val + "'");
+    throw invalidConversion(Type.DOUBLE);
   }
 
   private BigDecimal parseIntegerString(String tmpVal, int radix) {
diff --git a/src/test/java/com/healthmarketscience/jackcess/PropertyExpressionTest.java b/src/test/java/com/healthmarketscience/jackcess/PropertyExpressionTest.java
index d9f4932..ffb2dd3 100644
--- a/src/test/java/com/healthmarketscience/jackcess/PropertyExpressionTest.java
+++ b/src/test/java/com/healthmarketscience/jackcess/PropertyExpressionTest.java
@@ -294,7 +294,7 @@ public class PropertyExpressionTest extends TestCase
 
   public static void testCustomEvalConfig() throws Exception
   {
-    TemporalConfig tempConf = new TemporalConfig("yyyy/M/d",
+    TemporalConfig tempConf = new TemporalConfig("yyyy/M/d", "M/d",
                                                  "hh.mm.ss a",
                                                  "HH.mm.ss", '/', '.',
                                                  Locale.US);
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 575da31..c3f5f68 100644
--- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
+++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
@@ -17,6 +17,8 @@ limitations under the License.
 package com.healthmarketscience.jackcess.impl.expr;
 
 import java.math.BigDecimal;
+import java.util.Calendar;
+import java.util.Date;
 
 import com.healthmarketscience.jackcess.expr.EvalException;
 import junit.framework.TestCase;
@@ -34,8 +36,6 @@ public class DefaultFunctionsTest extends TestCase
     super(name);
   }
 
-  // FIXME, test more number/string functions
-
   public void testFuncs() throws Exception
   {
     assertEquals("foo", eval("=IIf(10 > 1, \"foo\", \"bar\")"));
@@ -78,15 +78,21 @@ public class DefaultFunctionsTest extends TestCase
                  eval("=CSng(\"57.12345\")"));
     assertEquals("9786", eval("=CStr(9786)"));
     assertEquals("-42", eval("=CStr(-42)"));
+    assertEquals(new Date(1041483600000L), eval("=CDate('01/02/2003')"));
+    assertEquals(new Date(1041508800000L), eval("=CDate('01/02/2003 7:00:00 AM')"));
+    assertEquals(new Date(-1948781520000L), eval("=CDate(3013.45)"));
+
 
     assertEquals(-1, eval("=IsNull(Null)"));
     assertEquals(0, eval("=IsNull(13)"));
     assertEquals(-1, eval("=IsDate(#01/02/2003#)"));
     assertEquals(0, eval("=IsDate('foo')"));
+    assertEquals(0, eval("=IsDate('200')"));
 
     assertEquals(0, eval("=IsNumeric(Null)"));
     assertEquals(0, eval("=IsNumeric('foo')"));
     assertEquals(0, eval("=IsNumeric(#01/02/2003#)"));
+    assertEquals(0, eval("=IsNumeric('01/02/2003')"));
     assertEquals(-1, eval("=IsNumeric(37)"));
     assertEquals(-1, eval("=IsNumeric(' 37 ')"));
     assertEquals(-1, eval("=IsNumeric(' -37.5e2 ')"));
@@ -245,6 +251,8 @@ public class DefaultFunctionsTest extends TestCase
 
     assertEquals(2003, eval("=Year('01/02/2003 7:00:00 AM')"));
     assertEquals(1899, eval("=Year(#7:00:00 AM#)"));
+    assertEquals(Calendar.getInstance().get(Calendar.YEAR),
+                 eval("=Year('01/02 7:00:00 AM')"));
 
     assertEquals("January", eval("=MonthName(1)"));
     assertEquals("Feb", eval("=MonthName(2,True)"));
@@ -280,6 +288,24 @@ public class DefaultFunctionsTest extends TestCase
     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))"));
+
+    assertEquals(1, eval("=DatePart('ww',#01/03/2018#)"));
+    assertEquals(2, eval("=DatePart('ww',#01/03/2018#,4)"));
+    assertEquals(1, eval("=DatePart('ww',#01/03/2018#,5)"));
+    assertEquals(1, eval("=DatePart('ww',#01/03/2018#,4,3)"));
+    assertEquals(52, eval("=DatePart('ww',#01/03/2018#,5,3)"));
+    assertEquals(1, eval("=DatePart('ww',#01/03/2018#,4,2)"));
+    assertEquals(53, eval("=DatePart('ww',#01/03/2018#,5,2)"));
+    assertEquals(2003, eval("=DatePart('yyyy',#11/22/2003 5:45:13 AM#)"));
+    assertEquals(4, eval("=DatePart('q',#11/22/2003 5:45:13 AM#)"));
+    assertEquals(11, eval("=DatePart('m',#11/22/2003 5:45:13 AM#)"));
+    assertEquals(326, eval("=DatePart('y',#11/22/2003 5:45:13 AM#)"));
+    assertEquals(22, eval("=DatePart('d',#11/22/2003 5:45:13 AM#)"));
+    assertEquals(7, eval("=DatePart('w',#11/22/2003 5:45:13 AM#)"));
+    assertEquals(3, eval("=DatePart('w',#11/22/2003 5:45:13 AM#, 5)"));
+    assertEquals(5, eval("=DatePart('h',#11/22/2003 5:45:13 AM#)"));
+    assertEquals(45, eval("=DatePart('n',#11/22/2003 5:45:13 AM#)"));
+    assertEquals(13, eval("=DatePart('s',#11/22/2003 5:45:13 AM#)"));
   }
 
   public void testFinancialFuncs() throws Exception