diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceSecondaryIndexInsertDeleteRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceSecondaryIndexInsertDeleteRule.java
index 4946680..c3d7bd9 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceSecondaryIndexInsertDeleteRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceSecondaryIndexInsertDeleteRule.java
@@ -24,6 +24,7 @@
 import java.util.LinkedHashSet;
 import java.util.List;
 import java.util.Map;
+import java.util.Objects;
 import java.util.Set;
 import java.util.stream.Collectors;
 
@@ -42,7 +43,6 @@
 import org.apache.asterix.metadata.entities.InternalDatasetDetails;
 import org.apache.asterix.metadata.utils.ArrayIndexUtil;
 import org.apache.asterix.metadata.utils.IndexUtil;
-import org.apache.asterix.metadata.utils.TypeUtil;
 import org.apache.asterix.om.base.AInt32;
 import org.apache.asterix.om.base.AOrderedList;
 import org.apache.asterix.om.base.AString;
@@ -320,8 +320,12 @@
             ILogicalOperator replicateOutput;
             if (!index.getIndexType().equals(IndexType.ARRAY)) {
                 for (int i = 0; i < secondaryKeyFields.size(); i++) {
-                    IndexFieldId indexFieldId = new IndexFieldId(secondaryKeySources.get(i), secondaryKeyFields.get(i),
-                            secondaryKeyTypes.get(i).getTypeTag());
+                    IAType skType = secondaryKeyTypes.get(i);
+                    Integer skSrc = secondaryKeySources.get(i);
+                    List<String> skName = secondaryKeyFields.get(i);
+                    ARecordType sourceType = dataset.hasMetaPart()
+                            ? skSrc.intValue() == Index.RECORD_INDICATOR ? recType : metaType : recType;
+                    IndexFieldId indexFieldId = createIndexFieldId(index, skName, skType, skSrc, sourceType, sourceLoc);
                     LogicalVariable skVar = fieldVarsForNewRecord.get(indexFieldId);
                     secondaryKeyVars.add(skVar);
                     VariableReferenceExpression skVarRef = new VariableReferenceExpression(skVar);
@@ -818,22 +822,27 @@
                             String.valueOf(index.getIndexType()));
             }
             for (int i = 0; i < skNames.size(); i++) {
-                IndexFieldId indexFieldId =
-                        new IndexFieldId(indicators.get(i), skNames.get(i), skTypes.get(i).getTypeTag());
+                List<String> skName = skNames.get(i);
+                Integer skSrc = indicators.get(i);
+                IAType skType = skTypes.get(i);
+
+                ARecordType sourceType = dataset.hasMetaPart()
+                        ? skSrc.intValue() == Index.RECORD_INDICATOR ? recType : metaType : recType;
+                LogicalVariable sourceVar = dataset.hasMetaPart()
+                        ? skSrc.intValue() == Index.RECORD_INDICATOR ? recordVar : metaVar : recordVar;
+
+                IAType fieldType = sourceType.getSubFieldType(skName);
+                IndexFieldId indexFieldId = createIndexFieldId(index, skName, skType, skSrc, sourceType, sourceLoc);
                 if (fieldAccessVars.containsKey(indexFieldId)) {
                     // already handled in a different index
                     continue;
                 }
-                ARecordType sourceType = dataset.hasMetaPart()
-                        ? indicators.get(i).intValue() == Index.RECORD_INDICATOR ? recType : metaType : recType;
-                LogicalVariable sourceVar = dataset.hasMetaPart()
-                        ? indicators.get(i).intValue() == Index.RECORD_INDICATOR ? recordVar : metaVar : recordVar;
-                LogicalVariable fieldVar = context.newVar();
                 // create record variable ref
                 VariableReferenceExpression varRef = new VariableReferenceExpression(sourceVar);
                 varRef.setSourceLocation(sourceLoc);
-                IAType fieldType = sourceType.getSubFieldType(indexFieldId.fieldName);
+
                 AbstractFunctionCallExpression theFieldAccessFunc;
+                LogicalVariable fieldVar = context.newVar();
                 if (fieldType == null) {
                     // Open field. must prevent inlining to maintain the cast before the primaryOp and
                     // make handling of records with incorrect value type for this field easier and cleaner
@@ -842,7 +851,8 @@
                     AbstractFunctionCallExpression fieldAccessFunc =
                             getFieldAccessFunction(new MutableObject<>(varRef), -1, indexFieldId.fieldName);
                     // create cast
-                    theFieldAccessFunc = createCastExpression(index, skTypes.get(i), fieldAccessFunc, sourceLoc);
+                    theFieldAccessFunc = createCastExpression(index, skType, fieldAccessFunc, sourceLoc,
+                            indexFieldId.funId, indexFieldId.extraArg);
                 } else {
                     // Get the desired field position
                     int pos = indexFieldId.fieldName.size() > 1 ? -1
@@ -865,15 +875,38 @@
         return currentTop;
     }
 
+    private static IndexFieldId createIndexFieldId(Index index, List<String> skName, IAType skType, Integer skSrc,
+            ARecordType sourceType, SourceLocation srcLoc) throws AlgebricksException {
+        IAType fieldType = sourceType.getSubFieldType(skName);
+        FunctionIdentifier skFun = null;
+        String fmtArg = null;
+        if (fieldType == null) {
+            Pair<FunctionIdentifier, String> castExpr = getCastExpression(index, skType, srcLoc);
+            skFun = castExpr.first;
+            fmtArg = castExpr.second;
+        }
+        return new IndexFieldId(skSrc, skName, skType.getTypeTag(), skFun, fmtArg);
+    }
+
+    private static Pair<FunctionIdentifier, String> getCastExpression(Index index, IAType skType, SourceLocation srcLoc)
+            throws AlgebricksException {
+        if (IndexUtil.castDefaultNull(index)) {
+            return IndexUtil.getTypeConstructorDefaultNull(index, skType, srcLoc);
+        } else if (index.isEnforced()) {
+            return new Pair<>(BuiltinFunctions.CAST_TYPE, null);
+        } else {
+            return new Pair<>(BuiltinFunctions.CAST_TYPE_LAX, null);
+        }
+    }
+
     private AbstractFunctionCallExpression createCastExpression(Index index, IAType targetType,
-            AbstractFunctionCallExpression inputExpr, SourceLocation sourceLoc) throws CompilationException {
+            AbstractFunctionCallExpression inputExpr, SourceLocation sourceLoc, FunctionIdentifier castFun,
+            String fmtArg) throws CompilationException {
         ScalarFunctionCallExpression castExpr;
         if (IndexUtil.castDefaultNull(index)) {
-            castExpr = constructorFunction(targetType, inputExpr, sourceLoc);
-        } else if (index.isEnforced()) {
-            castExpr = castFunction(BuiltinFunctions.CAST_TYPE, targetType, inputExpr, sourceLoc);
+            castExpr = castConstructorFunction(castFun, fmtArg, inputExpr, sourceLoc);
         } else {
-            castExpr = castFunction(BuiltinFunctions.CAST_TYPE_LAX, targetType, inputExpr, sourceLoc);
+            castExpr = castFunction(castFun, targetType, inputExpr, sourceLoc);
         }
         return castExpr;
     }
@@ -888,17 +921,18 @@
         return castExpr;
     }
 
-    private ScalarFunctionCallExpression constructorFunction(IAType requiredType,
-            AbstractFunctionCallExpression inputExpr, SourceLocation sourceLoc) throws CompilationException {
-        FunctionIdentifier typeConstructorFun = TypeUtil.getTypeConstructorDefaultNull(requiredType);
-        if (typeConstructorFun == null) {
-            throw new CompilationException(ErrorCode.COMPILATION_TYPE_UNSUPPORTED, sourceLoc, "index",
-                    requiredType.getTypeName());
-        }
+    private ScalarFunctionCallExpression castConstructorFunction(FunctionIdentifier typeConstructorFun, String fmt,
+            AbstractFunctionCallExpression inputExpr, SourceLocation srcLoc) {
         BuiltinFunctionInfo typeConstructorInfo = BuiltinFunctions.getBuiltinFunctionInfo(typeConstructorFun);
         ScalarFunctionCallExpression constructorExpr = new ScalarFunctionCallExpression(typeConstructorInfo);
         constructorExpr.getArguments().add(new MutableObject<>(inputExpr));
-        constructorExpr.setSourceLocation(sourceLoc);
+        // add the format argument if specified
+        if (fmt != null) {
+            ConstantExpression fmtExpr = new ConstantExpression(new AsterixConstantValue(new AString(fmt)));
+            fmtExpr.setSourceLocation(srcLoc);
+            constructorExpr.getArguments().add(new MutableObject<>(fmtExpr));
+        }
+        constructorExpr.setSourceLocation(srcLoc);
         return constructorExpr;
     }
 
@@ -1157,15 +1191,20 @@
         }
     }
 
-    private final class IndexFieldId {
+    private static class IndexFieldId {
         private final int indicator;
         private final List<String> fieldName;
         private final ATypeTag fieldType;
+        private final FunctionIdentifier funId;
+        private final String extraArg; // currently, only for datetime constructor functions with the format arg
 
-        private IndexFieldId(int indicator, List<String> fieldName, ATypeTag fieldType) {
+        private IndexFieldId(int indicator, List<String> fieldName, ATypeTag fieldType, FunctionIdentifier funId,
+                String extraArg) {
             this.indicator = indicator;
             this.fieldName = fieldName;
             this.fieldType = fieldType;
+            this.funId = funId;
+            this.extraArg = extraArg;
         }
 
         @Override
@@ -1173,6 +1212,8 @@
             int result = indicator;
             result = 31 * result + fieldName.hashCode();
             result = 31 * result + fieldType.hashCode();
+            result = 31 * result + Objects.hashCode(funId);
+            result = 31 * result + Objects.hashCode(extraArg);
             return result;
         }
 
@@ -1185,13 +1226,9 @@
                 return false;
             }
             IndexFieldId that = (IndexFieldId) o;
-            if (indicator != that.indicator) {
-                return false;
-            }
-            if (!fieldName.equals(that.fieldName)) {
-                return false;
-            }
-            return fieldType == that.fieldType;
+            return indicator == that.indicator && Objects.equals(fieldName, that.fieldName)
+                    && fieldType == that.fieldType && Objects.equals(funId, that.funId)
+                    && Objects.equals(extraArg, that.extraArg);
         }
     }
 }
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
index 409e2bd..819922b 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
@@ -718,7 +718,7 @@
                     && index.getPendingOp() == MetadataUtil.PENDING_NO_OP) {
                 IAType indexedType = keyFieldTypes.get(keyIdx);
                 List<AbstractFunctionCallExpression> stepExprs = optFuncExpr.getStepsExprs(varIdx);
-                if (acceptSteps(accessMethod, stepExprs, indexedType, hasCastDefaultNull)) {
+                if (acceptSteps(index, accessMethod, stepExprs, indexedType, hasCastDefaultNull)) {
                     indexCandidates.add(index);
                     boolean isFieldTypeUnknown = fieldType == BuiltinType.AMISSING || fieldType == BuiltinType.ANY;
                     if (isFieldTypeUnknown && (!isOverridingKeyFieldTypes || index.isEnforced())) {
@@ -735,10 +735,10 @@
         return true;
     }
 
-    private boolean acceptSteps(IAccessMethod accessMethod, List<AbstractFunctionCallExpression> stepExprs,
+    private boolean acceptSteps(Index index, IAccessMethod accessMethod, List<AbstractFunctionCallExpression> stepExprs,
             IAType indexedType, boolean indexHasCastDefaultNull) throws AlgebricksException {
         for (int i = stepExprs.size() - 1; i >= 0; i--) {
-            if (!accessMethod.acceptsFunction(stepExprs.get(i), indexedType, indexHasCastDefaultNull, i == 0)) {
+            if (!accessMethod.acceptsFunction(stepExprs.get(i), index, indexedType, indexHasCastDefaultNull, i == 0)) {
                 return false;
             }
         }
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/ArrayBTreeAccessMethod.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/ArrayBTreeAccessMethod.java
index 1274620..c633559 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/ArrayBTreeAccessMethod.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/ArrayBTreeAccessMethod.java
@@ -250,7 +250,7 @@
     }
 
     @Override
-    public boolean acceptsFunction(AbstractFunctionCallExpression functionExpr, IAType indexedFieldType,
+    public boolean acceptsFunction(AbstractFunctionCallExpression functionExpr, Index index, IAType indexedFieldType,
             boolean defaultNull, boolean finalStep) throws CompilationException {
         if (defaultNull) {
             throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, "CAST modifier not allowed");
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java
index a43f0f9..ef73905 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java
@@ -29,6 +29,7 @@
 import java.util.HashSet;
 import java.util.Iterator;
 import java.util.List;
+import java.util.Objects;
 import java.util.Set;
 
 import org.apache.asterix.common.annotations.AbstractExpressionAnnotationWithIndexNames;
@@ -41,6 +42,7 @@
 import org.apache.asterix.lang.common.util.FunctionUtil;
 import org.apache.asterix.metadata.entities.Dataset;
 import org.apache.asterix.metadata.entities.Index;
+import org.apache.asterix.metadata.utils.IndexUtil;
 import org.apache.asterix.metadata.utils.TypeUtil;
 import org.apache.asterix.om.functions.BuiltinFunctions;
 import org.apache.asterix.om.types.ARecordType;
@@ -1040,7 +1042,7 @@
     }
 
     @Override
-    public boolean acceptsFunction(AbstractFunctionCallExpression functionExpr, IAType indexedFieldType,
+    public boolean acceptsFunction(AbstractFunctionCallExpression functionExpr, Index index, IAType indexedFieldType,
             boolean defaultNull, boolean finalStep) throws CompilationException {
         FunctionIdentifier funId = functionExpr.getFunctionIdentifier();
         if (!finalStep) {
@@ -1051,9 +1053,13 @@
                 return false;
             }
             IAType nonNullableType = Index.getNonNullableType(indexedFieldType).first;
-            FunctionIdentifier indexedFieldConstructor = TypeUtil.getTypeConstructorDefaultNull(nonNullableType);
+            Pair<FunctionIdentifier, String> constructorWithFmt =
+                    IndexUtil.getTypeConstructorDefaultNull(index, nonNullableType, functionExpr.getSourceLocation());
+            FunctionIdentifier indexedFieldConstructorFun = constructorWithFmt.first;
+            String formatInIndex = constructorWithFmt.second;
+            String formatInFunction = TypeUtil.getTemporalFormatArg(functionExpr);
             // index has CAST (DEFAULT NULL); the applied function should be the same as the indexed field function
-            return funId.equals(indexedFieldConstructor);
+            return funId.equals(indexedFieldConstructorFun) && Objects.equals(formatInIndex, formatInFunction);
         } else {
             return AccessMethodUtils.isFieldAccess(funId);
         }
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IAccessMethod.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IAccessMethod.java
index 8ea2d37..e7becfa 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IAccessMethod.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IAccessMethod.java
@@ -135,12 +135,13 @@
      * Checks whether the function applied to an indexed field is acceptable by the access method.
      *
      * @param functionExpr applied function
+     * @param index the index definition
      * @param indexedFieldType the type of the indexed field in the index definition
      * @param defaultNull true if the candidate index has CAST (DEFAULT NULL) modifier
      * @param finalStep true if the functionExpr is the final function applied
      *
      * @return true if the access method accepts the argument function. False, otherwise.
      */
-    public boolean acceptsFunction(AbstractFunctionCallExpression functionExpr, IAType indexedFieldType,
+    public boolean acceptsFunction(AbstractFunctionCallExpression functionExpr, Index index, IAType indexedFieldType,
             boolean defaultNull, boolean finalStep) throws AlgebricksException;
 }
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/InvertedIndexAccessMethod.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/InvertedIndexAccessMethod.java
index 6fd1290..a5a2907 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/InvertedIndexAccessMethod.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/InvertedIndexAccessMethod.java
@@ -1350,7 +1350,7 @@
     }
 
     @Override
-    public boolean acceptsFunction(AbstractFunctionCallExpression functionExpr, IAType indexedFieldType,
+    public boolean acceptsFunction(AbstractFunctionCallExpression functionExpr, Index index, IAType indexedFieldType,
             boolean defaultNull, boolean finalStep) throws CompilationException {
         if (defaultNull) {
             throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, "CAST modifier not allowed");
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/RTreeAccessMethod.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/RTreeAccessMethod.java
index a61738e..73b331c 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/RTreeAccessMethod.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/RTreeAccessMethod.java
@@ -397,7 +397,7 @@
     }
 
     @Override
-    public boolean acceptsFunction(AbstractFunctionCallExpression functionExpr, IAType indexedFieldType,
+    public boolean acceptsFunction(AbstractFunctionCallExpression functionExpr, Index index, IAType indexedFieldType,
             boolean defaultNull, boolean finalStep) throws CompilationException {
         if (defaultNull) {
             throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, "CAST modifier not allowed");
diff --git a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/function/DumpIndexReader.java b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/function/DumpIndexReader.java
index 1ed57c0..aed15c6 100644
--- a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/function/DumpIndexReader.java
+++ b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/function/DumpIndexReader.java
@@ -38,6 +38,7 @@
 import org.apache.hyracks.storage.common.IIndexAccessor;
 import org.apache.hyracks.storage.common.IIndexCursor;
 import org.apache.hyracks.storage.common.MultiComparator;
+import org.apache.hyracks.util.JSONUtil;
 
 public class DumpIndexReader extends FunctionReader {
 
@@ -101,13 +102,29 @@
         for (int j = 0; j < tuple.getFieldCount(); ++j) {
             bbis.setByteBuffer(ByteBuffer.wrap(tuple.getFieldData(j)), tuple.getFieldStart(j));
             IAObject field = (IAObject) secondaryRecDesc.getFields()[j].deserialize(dis);
-            if (field.getType().getTypeTag() == ATypeTag.MISSING) {
+            ATypeTag tag = field.getType().getTypeTag();
+            if (tag == ATypeTag.MISSING) {
                 continue;
             }
-            recordBuilder.append(field);
+            if (isTemporal(tag)) {
+                JSONUtil.quoteAndEscape(recordBuilder, field.toString());
+            } else {
+                recordBuilder.append(field);
+            }
             recordBuilder.append(",");
         }
         recordBuilder.deleteCharAt(recordBuilder.length() - 1);
         recordBuilder.append("]}");
     }
+
+    private static boolean isTemporal(ATypeTag typeTag) {
+        switch (typeTag) {
+            case DATE:
+            case TIME:
+            case DATETIME:
+                return true;
+            default:
+                return false;
+        }
+    }
 }
diff --git a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
index d298c7f..00138ed 100644
--- a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
+++ b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
@@ -1339,9 +1339,14 @@
                 }
                 switch (Index.IndexCategory.of(indexType)) {
                     case VALUE:
+                        Map<String, String> castConfig = TypeUtil.validateConfiguration(stmtCreateIndex.getCastConfig(),
+                                stmtCreateIndex.getSourceLocation());
+                        String datetimeFormat = TypeUtil.getDatetimeFormat(castConfig);
+                        String dateFormat = TypeUtil.getDateFormat(castConfig);
+                        String timeFormat = TypeUtil.getTimeFormat(castConfig);
                         indexDetails = new Index.ValueIndexDetails(keyFieldNames, keyFieldSourceIndicators,
                                 keyFieldTypes, overridesFieldTypes, stmtCreateIndex.getExcludeUnknownKey(),
-                                stmtCreateIndex.getCastDefaultNull());
+                                stmtCreateIndex.getCastDefaultNull(), datetimeFormat, dateFormat, timeFormat);
                         break;
                     case TEXT:
                         indexDetails = new Index.TextIndexDetails(keyFieldNames, keyFieldSourceIndicators,
@@ -1549,20 +1554,19 @@
                     // Get snapshot from External File System
                     externalFilesSnapshot = ExternalIndexingOperations.getSnapshotFromExternalFileSystem(ds);
                     // Add an entry for the files index
-                    Index.IndexCategory indexCategory = Index.IndexCategory.of(index.getIndexType());
-                    OptionalBoolean excludeUnknownKey = OptionalBoolean.empty();
-                    if (indexCategory == Index.IndexCategory.VALUE) {
-                        excludeUnknownKey = ((Index.ValueIndexDetails) index.getIndexDetails()).getExcludeUnknownKey();
-                    }
-                    OptionalBoolean castDefaultNull = OptionalBoolean.empty();
-                    if (indexCategory == Index.IndexCategory.VALUE) {
-                        castDefaultNull = ((Index.ValueIndexDetails) index.getIndexDetails()).getCastDefaultNull();
-                    }
+                    OptionalBoolean excludeUnknownKey =
+                            ((Index.ValueIndexDetails) index.getIndexDetails()).getExcludeUnknownKey();
+                    OptionalBoolean castDefaultNull =
+                            ((Index.ValueIndexDetails) index.getIndexDetails()).getCastDefaultNull();
+                    String datetimeFormat = ((Index.ValueIndexDetails) index.getIndexDetails()).getCastDatetimeFormat();
+                    String dateFormat = ((Index.ValueIndexDetails) index.getIndexDetails()).getCastDateFormat();
+                    String timeFormat = ((Index.ValueIndexDetails) index.getIndexDetails()).getCastTimeFormat();
+
                     filesIndex = new Index(index.getDataverseName(), index.getDatasetName(),
                             IndexingConstants.getFilesIndexName(index.getDatasetName()), IndexType.BTREE,
                             new Index.ValueIndexDetails(ExternalIndexingOperations.FILE_INDEX_FIELD_NAMES, null,
                                     ExternalIndexingOperations.FILE_INDEX_FIELD_TYPES, false, excludeUnknownKey,
-                                    castDefaultNull),
+                                    castDefaultNull, datetimeFormat, dateFormat, timeFormat),
                             false, false, MetadataUtil.PENDING_ADD_OP);
                     MetadataManager.INSTANCE.addIndex(metadataProvider.getMetadataTxnContext(), filesIndex);
                     // Add files to the external files index
@@ -2630,10 +2634,10 @@
                 }
 
                 Map<String, String> viewConfig =
-                        ViewUtil.validateViewConfiguration(cvs.getViewConfiguration(), cvs.getSourceLocation());
-                datetimeFormat = ViewUtil.getDatetimeFormat(viewConfig);
-                dateFormat = ViewUtil.getDateFormat(viewConfig);
-                timeFormat = ViewUtil.getTimeFormat(viewConfig);
+                        TypeUtil.validateConfiguration(cvs.getViewConfiguration(), cvs.getSourceLocation());
+                datetimeFormat = TypeUtil.getDatetimeFormat(viewConfig);
+                dateFormat = TypeUtil.getDateFormat(viewConfig);
+                timeFormat = TypeUtil.getTimeFormat(viewConfig);
 
             } else {
                 if (primaryKeyDecl != null) {
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-05.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-05.sqlpp
index cc050e4..d5d2e21 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-05.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-05.sqlpp
@@ -31,7 +31,7 @@
 
 
 USE test;
-// test that idx2 on view2(x) is used. both v4.x and v2.x are int and idx2 is on x as int.
+// test that idx2 on view2(x) is used. both v4.x and v2.x are int and both v2.x and idx2 use int-default-null.
 SELECT v4.x AS v4x, v2.x AS v2x
 FROM view4 AS v4, view2 AS v2
 WHERE v4.x /*+ indexnl */ = v2.x ORDER BY v4x, v2x;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-06.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-06.sqlpp
index 514c920..a5697b4 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-06.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-06.sqlpp
@@ -30,7 +30,7 @@
 CREATE VIEW view4(id int, x int, y int) DEFAULT NULL AS SELECT id, x, y FROM ds4;
 
 USE test;
-// test that idx3 on view3(x) is not used since v4.x is int, v3.x is string
+// test that idx3 on view3(x) is not used because v3.x uses string-default-null and idx3 uses int-default-null
 SELECT v4.x AS v4x, v3.x AS v3x
 FROM view4 AS v4, view3 AS v3
 WHERE v4.x /*+ indexnl */ = v3.x ORDER BY v4x, v3x;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-07.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-07.sqlpp
index 3b9ed4b..ad2a058 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-07.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-07.sqlpp
@@ -30,7 +30,7 @@
 CREATE VIEW view4(id int, x int, y int) DEFAULT NULL AS SELECT id, x, y FROM ds4;
 
 USE test;
-// test that idx1 is not used even though v4.x is int, v1.x is int because idx1 does not have CAST modifier.
+// test that idx1 is not used because idx1 does not have CAST modifier (idx1 is just int and v1.x uses int-default-null).
 SELECT v4.x AS v4x, v1.x AS v1x
 FROM view4 AS v4, view1 AS v1
 WHERE v4.x /*+ indexnl */ = v1.x ORDER BY v4x, v1x;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-08.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-08.sqlpp
index 3b9ed4b..ad2a058 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-08.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-08.sqlpp
@@ -30,7 +30,7 @@
 CREATE VIEW view4(id int, x int, y int) DEFAULT NULL AS SELECT id, x, y FROM ds4;
 
 USE test;
-// test that idx1 is not used even though v4.x is int, v1.x is int because idx1 does not have CAST modifier.
+// test that idx1 is not used because idx1 does not have CAST modifier (idx1 is just int and v1.x uses int-default-null).
 SELECT v4.x AS v4x, v1.x AS v1x
 FROM view4 AS v4, view1 AS v1
 WHERE v4.x /*+ indexnl */ = v1.x ORDER BY v4x, v1x;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-09.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-09.sqlpp
index e8eddb2..0ed83a8 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-09.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-09.sqlpp
@@ -29,7 +29,7 @@
 CREATE VIEW view4(id int, x int, y int) DEFAULT NULL AS SELECT id, x, y FROM ds4;
 
 USE test;
-// test that idx2 is not used because v4.x is int-default-null and ds2.x is int.
+// test that idx2 is not used because idx2 uses int-default-null and ds2.x is just int.
 SELECT v4.x AS v4x, ds2.x AS ds2x
 FROM view4 AS v4, ds2 AS ds2
 WHERE v4.x /*+ indexnl */ = ds2.x ORDER BY v4x, ds2x;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-10.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-10.sqlpp
new file mode 100644
index 0000000..937a61f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-10.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE DATASET ds5(id int not unknown) OPEN TYPE PRIMARY KEY id;
+
+CREATE INDEX idx5_dt_fmt ON ds5(f_dt_fmt: datetime) CAST (DEFAULT NULL datetime 'MM/DD/YYYY hh:mm:ss.nnna' date 'MM/DD/YYYY');
+
+CREATE VIEW view5_dt_fmt(id int, f_dt_fmt datetime) DEFAULT NULL
+datetime 'MM/DD/YYYY hh:mm:ss.nnna' date 'MM/DD/YYYY' time 'hh:mm:ss.nnna' AS SELECT id, f_dt_fmt FROM ds5;
+
+// testing that idx5_dt_fmt is selected
+SELECT VALUE v
+FROM view5_dt_fmt AS v
+WHERE v.f_dt_fmt < datetime('2020-12-20T00:00:00Z')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-11.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-11.sqlpp
new file mode 100644
index 0000000..a9e7652
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-11.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE DATASET ds5(id int not unknown) OPEN TYPE PRIMARY KEY id;
+
+CREATE INDEX idx5_d_fmt ON ds5(f_d_fmt: date) CAST (DEFAULT NULL date 'MM/DD/YYYY');
+
+CREATE VIEW view5_d_fmt(id int, f_d_fmt date) DEFAULT NULL
+datetime 'MM/DD/YYYY hh:mm:ss.nnna' date 'MM/DD/YYYY' time 'hh:mm:ss.nnna' AS SELECT id, f_d_fmt FROM ds5;
+
+// testing that idx5_d_fmt is selected
+SELECT VALUE v
+FROM view5_d_fmt AS v
+WHERE v.f_d_fmt < date('2020-12-20')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-12.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-12.sqlpp
new file mode 100644
index 0000000..e97c4ea
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-12.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE DATASET ds5(id int not unknown) OPEN TYPE PRIMARY KEY id;
+
+CREATE INDEX idx5_t_fmt ON ds5(f_t_fmt: time) CAST (DEFAULT NULL time 'hh:mm:ss.nnna');
+
+CREATE VIEW view5_t_fmt(id int, f_t_fmt time) DEFAULT NULL
+datetime 'MM/DD/YYYY hh:mm:ss.nnna' date 'MM/DD/YYYY' time 'hh:mm:ss.nnna' AS SELECT id, f_t_fmt FROM ds5;
+
+// testing that idx5_t_fmt is selected
+SELECT VALUE v
+FROM view5_t_fmt AS v
+WHERE v.f_t_fmt < time('18:13:03')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-13.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-13.sqlpp
new file mode 100644
index 0000000..2d9d2ab
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-13.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE DATASET ds6(id int not unknown) OPEN TYPE PRIMARY KEY id;
+
+CREATE INDEX idx6_dt ON ds6(f_dt: datetime) CAST (DEFAULT NULL);
+
+CREATE VIEW view6_dt(id int, f_dt datetime) DEFAULT NULL AS SELECT id, f_dt FROM ds6;
+
+// testing that idx6_dt is selected
+SELECT VALUE v
+FROM view6_dt AS v
+WHERE v.f_dt < datetime('2020-12-20T00:00:00Z')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-14.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-14.sqlpp
new file mode 100644
index 0000000..79ee051
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-14.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE DATASET ds6(id int not unknown) OPEN TYPE PRIMARY KEY id;
+
+CREATE INDEX idx6_d ON ds6(f_d: date) CAST (DEFAULT NULL);
+
+CREATE VIEW view6_d(id int, f_d date) DEFAULT NULL AS SELECT id, f_d FROM ds6;
+
+// testing that idx6_d is selected
+SELECT VALUE v
+FROM view6_d AS v
+WHERE v.f_d < date('2020-12-20')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-15.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-15.sqlpp
new file mode 100644
index 0000000..2ec3f7e7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-15.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE DATASET ds6(id int not unknown) OPEN TYPE PRIMARY KEY id;
+
+CREATE INDEX idx6_t ON ds6(f_t: time) CAST (DEFAULT NULL);
+
+CREATE VIEW view6_t(id int, f_t time) DEFAULT NULL AS SELECT id, f_t FROM ds6;
+
+// testing that idx6_t is selected
+SELECT VALUE v
+FROM view6_t AS v
+WHERE v.f_t < time('18:13:03')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-16.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-16.sqlpp
new file mode 100644
index 0000000..ed213f8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-16.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE DATASET ds5(id int not unknown) OPEN TYPE PRIMARY KEY id;
+
+CREATE INDEX idx5_dt_fmt ON ds5(f_dt_fmt: datetime) CAST (DEFAULT NULL datetime 'MM/DD/YYYY hh:mm:ss.nnna' date 'MM/DD/YYYY');
+
+CREATE VIEW view5_dt_fmt(id int, f_dt_fmt datetime) DEFAULT NULL
+datetime 'MM-DD-YYYY hh:mm:ss.nnna' date 'MM/DD/YYYY' time 'hh:mm:ss.nnna' AS SELECT id, f_dt_fmt FROM ds5;
+
+// testing that the index idx5_dt_fmt is not selected because its datetime format is not the same as view view5_dt_fmt
+SELECT VALUE v
+FROM view5_dt_fmt AS v
+WHERE v.f_dt_fmt < datetime('2020-12-20T00:00:00Z')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-17.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-17.sqlpp
new file mode 100644
index 0000000..398f816
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-17.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE DATASET ds6(id int not unknown) OPEN TYPE PRIMARY KEY id;
+
+CREATE INDEX idx6_d_fmt ON ds6(f_d: date) CAST (DEFAULT NULL date 'MM/DD/YYYY');
+
+CREATE VIEW view6_d(id int, f_d date) DEFAULT NULL AS SELECT id, f_d FROM ds6;
+
+// testing that idx6_d_fmt is not selected because the date function in index is with format and the view without format
+SELECT VALUE v
+FROM view6_d AS v
+WHERE v.f_d < date('2020-12-20')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-18.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-18.sqlpp
new file mode 100644
index 0000000..b55f4e8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-18.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE DATASET ds5(id int not unknown) OPEN TYPE PRIMARY KEY id;
+
+CREATE INDEX idx5_t ON ds5(f_t_fmt: time) CAST (DEFAULT NULL);
+
+CREATE VIEW view5_t_fmt(id int, f_t_fmt time) DEFAULT NULL
+datetime 'MM/DD/YYYY hh:mm:ss.nnna' date 'MM/DD/YYYY' time 'hh:mm:ss.nnna' AS SELECT id, f_t_fmt FROM ds5;
+
+// testing that idx5_t is not selected because the time function in index is without format and the view with format
+SELECT VALUE v
+FROM view5_t_fmt AS v
+WHERE v.f_t_fmt < time('18:13:03')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-19.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-19.sqlpp
new file mode 100644
index 0000000..714bf05
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-index-selection/cast-default-null/cast-default-null-19.sqlpp
@@ -0,0 +1,41 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE DATASET ds5(id int not unknown) OPEN TYPE PRIMARY KEY id;
+
+CREATE INDEX idx5_dt_fmt ON ds5(f_dt_fmt: datetime) CAST (DEFAULT NULL datetime 'MM/DD/YYYY hh:mm:ss.nnna' date 'MM/DD/YYYY');
+
+CREATE VIEW view5_dt_fmt(id int, f_dt_fmt datetime) DEFAULT NULL
+datetime 'MM/DD/YYYY hh:mm:ss.nnna' date 'MM/DD/YYYY' time 'hh:mm:ss.nnna' AS SELECT id, f_dt_fmt FROM ds5;
+
+CREATE DATASET ds6(id int not unknown) OPEN TYPE PRIMARY KEY id;
+
+CREATE INDEX idx6_dt ON ds6(f_dt: datetime) CAST (DEFAULT NULL);
+
+CREATE VIEW view6_dt(id int, f_dt datetime) DEFAULT NULL AS SELECT id, f_dt FROM ds6;
+
+USE test;
+// testing that idx5_dt_fmt is used
+SELECT v6.f_dt AS v6f_dt, v5.f_dt_fmt AS v5f_dt_fmt
+FROM view6_dt AS v6, view5_dt_fmt AS v5
+WHERE v6.f_dt /*+ indexnl */ = v5.f_dt_fmt ORDER BY v6f_dt, v5f_dt_fmt;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-10.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-10.plan
new file mode 100644
index 0000000..19fe8ce
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-10.plan
@@ -0,0 +1,23 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$56(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_SELECT  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- BTREE_SEARCH (test.ds5.ds5)  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STABLE_SORT [$$59(ASC)]  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- BTREE_SEARCH (test.ds5.idx5_dt_fmt)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-11.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-11.plan
new file mode 100644
index 0000000..b636106
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-11.plan
@@ -0,0 +1,23 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$56(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_SELECT  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- BTREE_SEARCH (test.ds5.ds5)  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STABLE_SORT [$$59(ASC)]  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- BTREE_SEARCH (test.ds5.idx5_d_fmt)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-12.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-12.plan
new file mode 100644
index 0000000..382c39a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-12.plan
@@ -0,0 +1,23 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$56(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_SELECT  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- BTREE_SEARCH (test.ds5.ds5)  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STABLE_SORT [$$59(ASC)]  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- BTREE_SEARCH (test.ds5.idx5_t_fmt)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-13.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-13.plan
new file mode 100644
index 0000000..babda5c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-13.plan
@@ -0,0 +1,23 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$56(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_SELECT  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- BTREE_SEARCH (test.ds6.ds6)  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STABLE_SORT [$$59(ASC)]  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- BTREE_SEARCH (test.ds6.idx6_dt)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-14.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-14.plan
new file mode 100644
index 0000000..d55a9a2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-14.plan
@@ -0,0 +1,23 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$56(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_SELECT  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- BTREE_SEARCH (test.ds6.ds6)  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STABLE_SORT [$$59(ASC)]  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- BTREE_SEARCH (test.ds6.idx6_d)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-15.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-15.plan
new file mode 100644
index 0000000..2799b48
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-15.plan
@@ -0,0 +1,23 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$56(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_SELECT  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- BTREE_SEARCH (test.ds6.ds6)  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STABLE_SORT [$$59(ASC)]  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- BTREE_SEARCH (test.ds6.idx6_t)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-16.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-16.plan
new file mode 100644
index 0000000..6291577
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-16.plan
@@ -0,0 +1,16 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$56(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_SELECT  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- DATASOURCE_SCAN (test.ds5)  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-17.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-17.plan
new file mode 100644
index 0000000..8757f3e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-17.plan
@@ -0,0 +1,16 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$56(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_SELECT  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- DATASOURCE_SCAN (test.ds6)  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-18.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-18.plan
new file mode 100644
index 0000000..6291577
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-18.plan
@@ -0,0 +1,16 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$56(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_SELECT  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- DATASOURCE_SCAN (test.ds5)  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-19.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-19.plan
new file mode 100644
index 0000000..cb86b13
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-selection/cast-default-null/cast-default-null-19.plan
@@ -0,0 +1,27 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$110(ASC), $$111(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$110(ASC), $$111(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_SELECT  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ASSIGN  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- BTREE_SEARCH (test.ds5.ds5)  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- STABLE_SORT [$$123(ASC)]  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- BTREE_SEARCH (test.ds5.idx5_dt_fmt)  |PARTITIONED|
+                                      -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- DATASOURCE_SCAN (test.ds6)  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.005.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.005.ddl.sqlpp
new file mode 100644
index 0000000..bbabd10
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null-negative/index-cast-null-negative.005.ddl.sqlpp
@@ -0,0 +1,21 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+USE test;
+// invalid datetime property name
+CREATE INDEX idx_dt ON ds1(f_dt: datetime) CAST (DEFAULT NULL invalid_date 'MM/DD/YYYY');
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.000.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.000.ddl.sqlpp
index b810727..3178a11 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.000.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.000.ddl.sqlpp
@@ -24,8 +24,10 @@
 CREATE DATAVERSE test;
 USE test;
 CREATE TYPE t1 AS { id: int, s_f: string, d_f: double, i_f: int, b_f: boolean };
-CREATE DATASET ds1(t1) primary key id;
-CREATE DATASET ds2(t1) primary key id;
+CREATE DATASET ds1(t1) PRIMARY KEY id;
+CREATE DATASET ds2(t1) PRIMARY KEY id;
+CREATE DATASET ds3(id int not unknown) OPEN TYPE PRIMARY KEY id;
+CREATE DATASET ds4(id int not unknown) OPEN TYPE PRIMARY KEY id;
 
 //CREATE INDEX ds2_idx1 ON ds2(s_f: int) CAST (DEFAULT NULL);
 
@@ -37,4 +39,16 @@
 
 CREATE INDEX ds2_o_idx5 ON ds2(a.any_f: int) CAST (DEFAULT NULL);
 CREATE INDEX ds2_o_idx6 ON ds2(a.any_f: string) CAST (DEFAULT NULL);
-CREATE INDEX ds2_o_idx7 ON ds2(a.any_f: double) CAST (DEFAULT NULL);
\ No newline at end of file
+CREATE INDEX ds2_o_idx7 ON ds2(a.any_f: double) CAST (DEFAULT NULL);
+
+// temporal
+CREATE INDEX ds3_o_idx_f_dt ON ds3(f_dt: datetime) CAST (DEFAULT NULL);
+CREATE INDEX ds3_o_idx_f_d ON ds3(f_d: date) CAST (DEFAULT NULL);
+CREATE INDEX ds3_o_idx_f_t ON ds3(f_t: time) CAST (DEFAULT NULL);
+
+// temporal with format
+CREATE INDEX ds3_o_idx_f_dt_fmt ON ds3(f_dt_fmt: datetime) CAST (DEFAULT NULL datetime 'MM/DD/YYYY hh:mm:ss.nnna');
+CREATE INDEX ds3_o_idx_f_d_fmt ON ds3(f_d_fmt: date) CAST (DEFAULT NULL date 'MM/DD/YYYY');
+CREATE INDEX ds3_o_idx_f_t_fmt ON ds3(f_t_fmt: time) CAST (DEFAULT NULL time 'hh:mm:ss.nnna');
+
+CREATE INDEX ds3_o_idx_invalid_fmt ON ds3(f_d_fmt: date) CAST (DEFAULT NULL date 'invalid_format');
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.001.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.001.update.sqlpp
index eea08de..969f727 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.001.update.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.001.update.sqlpp
@@ -40,4 +40,24 @@
 {"id": 7, "s_f": "7.5",   "d_f": 7.5, "i_f": 7, "b_f": false, "o_s_f": "7.5",   "o_d_f": 7.5, "o_i_f": 7, "o_b_f": false, "a": {"s_f": "7.5",   "any_f": false}},
 {"id": 8, "s_f": "false", "d_f": 8.5, "i_f": 8, "b_f": false, "o_s_f": "false", "o_d_f": 8.5, "o_i_f": 8, "o_b_f": false, "a": {"s_f": "false", "any_f": [1,2]}},
 {"id": 9, "s_f": "false", "d_f": 9.5, "i_f": 9, "b_f": false}
+];
+
+INSERT INTO ds3 [
+{"id": 1, 'f_dt':'2020-02-20T01:40:41.001', 'f_d':'2020-02-20', 'f_t':'01:40:41.001', 'f_dt_fmt':'02/20/2020 01:40:41.001am', 'f_d_fmt':'02/20/2020', 'f_t_fmt':'01:40:41.001am'},
+{"id": 2, 'f_dt':'2021-11-25T22:12:51.999', 'f_d':'2021-11-25', 'f_t':'22:12:51.999', 'f_dt_fmt':'11/25/2021 10:12:51.999pm', 'f_d_fmt':'11/25/2021', 'f_t_fmt':'10:12:51.999pm'},
+/* Invalid value */
+{"id": 3, 'f_dt':'a', 'f_d':'b', 'f_t':'c', 'f_dt_fmt':'a', 'c_date':'b', 'f_t_fmt':'c'},
+{"id": 4, 'f_dt':'2019-01-20T14:40:41.001', 'f_d':'2019-01-20', 'f_t':'14:40:41.001', 'f_dt_fmt':'01/20/2019 02:40:41.001pm', 'f_d_fmt':'01/20/2019', 'f_t_fmt':'02:40:41.001pm'},
+{"id": 5, 'f_dt':null, 'f_d':null, 'f_t':null, 'f_dt_fmt':null, 'f_d_fmt':null, 'f_t_fmt':null},
+{"id": 6}
+];
+
+INSERT INTO ds4 [
+{"id": 1, 'f_dt':'2020-02-20T01:40:41.001', 'f_d':'2020-02-20', 'f_t':'01:40:41.001', 'f_dt_fmt':'02/20/2020 01:40:41.001am', 'f_d_fmt':'02/20/2020', 'f_t_fmt':'01:40:41.001am'},
+{"id": 2, 'f_dt':'2021-11-25T22:12:51.999', 'f_d':'2021-11-25', 'f_t':'22:12:51.999', 'f_dt_fmt':'11/25/2021 10:12:51.999pm', 'f_d_fmt':'11/25/2021', 'f_t_fmt':'10:12:51.999pm'},
+/* Invalid value */
+{"id": 3, 'f_dt':'a', 'f_d':'b', 'f_t':'c', 'f_dt_fmt':'a', 'c_date':'b', 'f_t_fmt':'c'},
+{"id": 4, 'f_dt':'2019-01-20T14:40:41.001', 'f_d':'2019-01-20', 'f_t':'14:40:41.001', 'f_dt_fmt':'01/20/2019 02:40:41.001pm', 'f_d_fmt':'01/20/2019', 'f_t_fmt':'02:40:41.001pm'},
+{"id": 5, 'f_dt':null, 'f_d':null, 'f_t':null, 'f_dt_fmt':null, 'f_d_fmt':null, 'f_t_fmt':null},
+{"id": 6}
 ];
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.002.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.002.ddl.sqlpp
index 7255983..ee2c025 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.002.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.002.ddl.sqlpp
@@ -32,4 +32,16 @@
 
 CREATE INDEX ds1_o_idx5 ON ds1(a.any_f: int) CAST(DEFAULT NULL);
 CREATE INDEX ds1_o_idx6 ON ds1(a.any_f: string) CAST(DEFAULT NULL);
-CREATE INDEX ds1_o_idx7 ON ds1(a.any_f: double) CAST(DEFAULT NULL);
\ No newline at end of file
+CREATE INDEX ds1_o_idx7 ON ds1(a.any_f: double) CAST(DEFAULT NULL);
+
+// temporal
+CREATE INDEX ds4_o_idx_f_dt ON ds4(f_dt: datetime) CAST (DEFAULT NULL);
+CREATE INDEX ds4_o_idx_f_d ON ds4(f_d: date) CAST (DEFAULT NULL);
+CREATE INDEX ds4_o_idx_f_t ON ds4(f_t: time) CAST (DEFAULT NULL);
+
+// temporal with format
+CREATE INDEX ds4_o_idx_f_dt_fmt ON ds4(f_dt_fmt: datetime) CAST (DEFAULT NULL datetime 'MM/DD/YYYY hh:mm:ss.nnna');
+CREATE INDEX ds4_o_idx_f_d_fmt ON ds4(f_d_fmt: date) CAST (DEFAULT NULL date 'MM/DD/YYYY');
+CREATE INDEX ds4_o_idx_f_t_fmt ON ds4(f_t_fmt: time) CAST (DEFAULT NULL time 'hh:mm:ss.nnna');
+
+CREATE INDEX ds4_o_idx_invalid_fmt ON ds4(f_d_fmt: date) CAST (DEFAULT NULL date 'invalid_format');
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.016.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.016.query.sqlpp
new file mode 100644
index 0000000..8881cb6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.016.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds3", "ds3_o_idx_f_d_fmt") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.017.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.017.query.sqlpp
new file mode 100644
index 0000000..558f3b4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.017.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds3", "ds3_o_idx_f_t_fmt") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.018.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.018.query.sqlpp
new file mode 100644
index 0000000..bf99064
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.018.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds3", "ds3_o_idx_f_dt_fmt") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.019.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.019.query.sqlpp
new file mode 100644
index 0000000..c37d984
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.019.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds3", "ds3_o_idx_f_d") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.020.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.020.query.sqlpp
new file mode 100644
index 0000000..e901b04
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.020.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds3", "ds3_o_idx_f_t") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.021.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.021.query.sqlpp
new file mode 100644
index 0000000..151d016
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.021.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds3", "ds3_o_idx_f_dt") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.022.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.022.query.sqlpp
new file mode 100644
index 0000000..3fcff2d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.022.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds3", "ds3_o_idx_invalid_fmt") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.023.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.023.query.sqlpp
new file mode 100644
index 0000000..2c6339d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.023.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds4", "ds4_o_idx_f_d_fmt") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.024.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.024.query.sqlpp
new file mode 100644
index 0000000..be5e2ee
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.024.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds4", "ds4_o_idx_f_t_fmt") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.025.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.025.query.sqlpp
new file mode 100644
index 0000000..99551de
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.025.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds4", "ds4_o_idx_f_dt_fmt") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.026.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.026.query.sqlpp
new file mode 100644
index 0000000..7f407ca
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.026.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds4", "ds4_o_idx_f_d") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.027.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.027.query.sqlpp
new file mode 100644
index 0000000..d75187f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.027.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds4", "ds4_o_idx_f_t") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.028.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.028.query.sqlpp
new file mode 100644
index 0000000..6f2eb21
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.028.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds4", "ds4_o_idx_f_dt") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.029.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.029.query.sqlpp
new file mode 100644
index 0000000..e4d5557
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.029.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `import-private-functions` `true`;
+FROM DUMP_INDEX("test", "ds4", "ds4_o_idx_invalid_fmt") AS v
+SELECT VALUE v
+ORDER BY v.values;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.030.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.030.query.sqlpp
new file mode 100644
index 0000000..aeaffe8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.030.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+// check the index metadata
+USE test;
+
+FROM Metadata.`Index` v
+WHERE v.DatasetName = 'ds3' AND v.IsPrimary = false
+SELECT v.IndexName, v.`Cast`
+ORDER BY v.IndexName;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.031.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.031.query.sqlpp
new file mode 100644
index 0000000..437de9c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/index-cast-null/index-cast-null.031.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+// check the index metadata
+USE test;
+
+FROM Metadata.`Index` v
+WHERE v.DatasetName = 'ds4' AND v.IsPrimary = false
+SELECT v.IndexName, v.`Cast`
+ORDER BY v.IndexName;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.01.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.01.ddl.sqlpp
index e354d1d..98e54e3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.01.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.01.ddl.sqlpp
@@ -25,14 +25,35 @@
 CREATE DATASET ds2(id int not unknown, a string, b int) OPEN TYPE PRIMARY KEY id;
 CREATE DATASET ds3(id int not unknown, a string, b int) OPEN TYPE PRIMARY KEY id;
 CREATE DATASET ds4(id int not unknown, a string, b int) OPEN TYPE PRIMARY KEY id;
-CREATE DATASET ds5(id int not unknown, a string, b int) OPEN TYPE PRIMARY KEY id;
+CREATE DATASET ds5(id int not unknown) OPEN TYPE PRIMARY KEY id;
+CREATE DATASET ds6(id int not unknown) OPEN TYPE PRIMARY KEY id;
 
 CREATE INDEX idx1 ON ds1(x: int);
 CREATE INDEX idx2 ON ds2(x: int) CAST (DEFAULT NULL);
 CREATE INDEX idx3 ON ds3(x: int) CAST (DEFAULT NULL);
 
+CREATE INDEX idx5_dt_fmt ON ds5(f_dt_fmt: datetime) CAST (DEFAULT NULL datetime 'MM/DD/YYYY hh:mm:ss.nnna' date 'MM/DD/YYYY');
+CREATE INDEX idx5_d_fmt ON ds5(f_d_fmt: date) CAST (DEFAULT NULL date 'MM/DD/YYYY');
+CREATE INDEX idx5_t_fmt ON ds5(f_t_fmt: time) CAST (DEFAULT NULL time 'hh:mm:ss.nnna');
+
+CREATE INDEX idx6_dt ON ds6(f_dt: datetime) CAST (DEFAULT NULL);
+CREATE INDEX idx6_d ON ds6(f_d: date) CAST (DEFAULT NULL);
+CREATE INDEX idx6_t ON ds6(f_t: time) CAST (DEFAULT NULL);
 
 CREATE VIEW view1(id int, x int, y int) DEFAULT NULL AS SELECT id, x, y FROM ds1;
 CREATE VIEW view2(id int, x int, y int) DEFAULT NULL AS SELECT id, x, y FROM ds2;
 CREATE VIEW view3(id int, x string, y int) DEFAULT NULL AS SELECT id, x, y FROM ds3;
-CREATE VIEW view4(id int, x int, y int) DEFAULT NULL AS SELECT id, x, y FROM ds4;
\ No newline at end of file
+CREATE VIEW view4(id int, x int, y int) DEFAULT NULL AS SELECT id, x, y FROM ds4;
+
+CREATE VIEW view5_dt_fmt(id int, f_dt_fmt datetime) DEFAULT NULL
+datetime 'MM/DD/YYYY hh:mm:ss.nnna' date 'MM/DD/YYYY' time 'hh:mm:ss.nnna' AS SELECT id, f_dt_fmt FROM ds5;
+
+CREATE VIEW view5_d_fmt(id int, f_d_fmt date) DEFAULT NULL
+datetime 'MM/DD/YYYY hh:mm:ss.nnna' date 'MM/DD/YYYY' time 'hh:mm:ss.nnna' AS SELECT id, f_d_fmt FROM ds5;
+
+CREATE VIEW view5_t_fmt(id int, f_t_fmt time) DEFAULT NULL
+datetime 'MM/DD/YYYY hh:mm:ss.nnna' date 'MM/DD/YYYY' time 'hh:mm:ss.nnna' AS SELECT id, f_t_fmt FROM ds5;
+
+CREATE VIEW view6_dt(id int, f_dt datetime) DEFAULT NULL AS SELECT id, f_dt FROM ds6;
+CREATE VIEW view6_d(id int, f_d date) DEFAULT NULL AS SELECT id, f_d FROM ds6;
+CREATE VIEW view6_t(id int, f_t time) DEFAULT NULL AS SELECT id, f_t FROM ds6;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.02.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.02.update.sqlpp
index d47aa46..a7d6ba6 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.02.update.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.02.update.sqlpp
@@ -56,10 +56,21 @@
 ];
 
 INSERT INTO ds5 [
-{"id": 1, "a": "s1", "b": 1, "x": 1, "y": 2},
-{"id": 2, "a": "s2", "b": 2, "x": 2, "y": 1},
-{"id": 3, "a": "s3", "b": 3, "x": 1.1, "y": 2.1},
-{"id": 4, "a": "s4", "b": 4, "x": 2.1, "y": 1.1},
-{"id": 5, "a": "s5", "b": 5,           "y": 1.1},
-{"id": 6, "a": "s6", "b": 6, "x": 33333, "y": 3}
+{"id": 1, 'f_dt_fmt':'02/20/2020 01:40:41.001am', 'f_d_fmt':'02/20/2020', 'f_t_fmt':'01:40:41.001am'},
+{"id": 2, 'f_dt_fmt':'11/25/2021 10:12:51.999pm', 'f_d_fmt':'11/25/2021', 'f_t_fmt':'10:12:51.999pm'},
+/* Invalid value */
+{"id": 3, 'f_dt_fmt':'a', 'c_date':'b', 'f_t_fmt':'c'},
+{"id": 4, 'f_dt_fmt':'01/20/2019 02:40:41.001pm', 'f_d_fmt':'01/20/2019', 'f_t_fmt':'02:40:41.001pm'},
+{"id": 5, 'f_dt_fmt':null, 'f_d_fmt':null, 'f_t_fmt':null},
+{"id": 6}
+];
+
+INSERT INTO ds6 [
+{"id": 1, 'f_dt':'2020-02-20T01:40:41.001', 'f_d':'2020-02-20', 'f_t':'01:40:41.001'},
+{"id": 2, 'f_dt':'2021-11-25T22:12:51.999', 'f_d':'2021-11-25', 'f_t':'22:12:51.999'},
+/* Invalid value */
+{"id": 3, 'f_dt':'a', 'f_d':'b', 'f_t':'c'},
+{"id": 4, 'f_dt':'2019-01-20T14:40:41.001', 'f_d':'2019-01-20', 'f_t':'14:40:41.001'},
+{"id": 5, 'f_dt':null, 'f_d':null, 'f_t':null},
+{"id": 6}
 ];
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.07.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.07.query.sqlpp
index 103b285..e5016c9 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.07.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.07.query.sqlpp
@@ -18,7 +18,7 @@
  */
 
 USE test;
-// test that idx2 on view2(x) is used. both v4.x and v2.x are int and idx2 is on x as int.
+// test that idx2 on view2(x) is used. both v4.x and v2.x are int and both v2.x and idx2 use int-default-null.
 SELECT v4.x AS v4x, v2.x AS v2x
 FROM view4 AS v4, view2 AS v2
 WHERE v4.x /*+ indexnl */ = v2.x ORDER BY v4x, v2x;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.08.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.08.query.sqlpp
index f818b2b..2242a50 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.08.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.08.query.sqlpp
@@ -18,7 +18,7 @@
  */
 
 USE test;
-// test that idx3 on view3(x) is not used since v4.x is int, v3.x is string
+// test that idx3 on view3(x) is not used because v3.x uses string-default-null and idx3 uses int-default-null
 SELECT v4.x AS v4x, v3.x AS v3x
 FROM view4 AS v4, view3 AS v3
 WHERE v4.x /*+ indexnl */ = v3.x ORDER BY v4x, v3x;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.09.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.09.query.sqlpp
index 3aef10a..5be7fc9 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.09.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.09.query.sqlpp
@@ -18,7 +18,7 @@
  */
 
 USE test;
-// test that idx1 is not used even though v4.x is int, v1.x is int because idx1 does not have CAST modifier.
+// test that idx1 is not used because idx1 does not have CAST modifier (idx1 is just int and v1.x uses int-default-null).
 SELECT v4.x AS v4x, v1.x AS v1x
 FROM view4 AS v4, view1 AS v1
 WHERE v4.x /*+ indexnl */ = v1.x ORDER BY v4x, v1x;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.10.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.10.query.sqlpp
index 3aef10a..5be7fc9 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.10.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.10.query.sqlpp
@@ -18,7 +18,7 @@
  */
 
 USE test;
-// test that idx1 is not used even though v4.x is int, v1.x is int because idx1 does not have CAST modifier.
+// test that idx1 is not used because idx1 does not have CAST modifier (idx1 is just int and v1.x uses int-default-null).
 SELECT v4.x AS v4x, v1.x AS v1x
 FROM view4 AS v4, view1 AS v1
 WHERE v4.x /*+ indexnl */ = v1.x ORDER BY v4x, v1x;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.11.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.11.query.sqlpp
index 4da0b66..59786c2 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.11.query.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.11.query.sqlpp
@@ -18,7 +18,7 @@
  */
 
 USE test;
-// test that idx2 is not used because v4.x is int-default-null and ds2.x is int.
+// test that idx2 is not used because idx2 uses int-default-null and ds2.x is just int.
 SELECT v4.x AS v4x, ds2.x AS ds2x
 FROM view4 AS v4, ds2 AS ds2
 WHERE v4.x /*+ indexnl */ = ds2.x ORDER BY v4x, ds2x;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.12.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.12.query.sqlpp
new file mode 100644
index 0000000..c9de53b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.12.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SELECT VALUE v
+FROM view5_dt_fmt AS v
+WHERE v.f_dt_fmt < datetime('2020-12-20T00:00:00Z')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.13.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.13.query.sqlpp
new file mode 100644
index 0000000..faae08d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.13.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SELECT VALUE v
+FROM view5_d_fmt AS v
+WHERE v.f_d_fmt < date('2020-12-20')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.14.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.14.query.sqlpp
new file mode 100644
index 0000000..3c775d9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.14.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SELECT VALUE v
+FROM view5_t_fmt AS v
+WHERE v.f_t_fmt < time('18:13:03')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.15.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.15.query.sqlpp
new file mode 100644
index 0000000..e9ae3b4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.15.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SELECT VALUE v
+FROM view6_dt AS v
+WHERE v.f_dt < datetime('2020-12-20T00:00:00Z')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.16.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.16.query.sqlpp
new file mode 100644
index 0000000..5871f2f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.16.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SELECT VALUE v
+FROM view6_d AS v
+WHERE v.f_d < date('2020-12-20')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.17.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.17.query.sqlpp
new file mode 100644
index 0000000..93f1c88
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.17.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SELECT VALUE v
+FROM view6_t AS v
+WHERE v.f_t < time('18:13:03')
+ORDER BY v.id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.18.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.18.query.sqlpp
new file mode 100644
index 0000000..29081f2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/cast-default-null/cast-default-null.18.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+// testing that idx5_dt_fmt is used
+SELECT v6.f_dt AS v6f_dt, v5.f_dt_fmt AS v5f_dt_fmt
+FROM view6_dt AS v6, view5_dt_fmt AS v5
+WHERE v6.f_dt /*+ indexnl */ = v5.f_dt_fmt ORDER BY v6f_dt, v5f_dt_fmt;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.016.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.016.adm
new file mode 100644
index 0000000..2cbfa87
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.016.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 3 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
+{ "values": [ "\"date\": { 2019-01-20 }", 4 ] }
+{ "values": [ "\"date\": { 2020-02-20 }", 1 ] }
+{ "values": [ "\"date\": { 2021-11-25 }", 2 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.017.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.017.adm
new file mode 100644
index 0000000..38633f5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.017.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 3 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
+{ "values": [ "time: { 01:40:41.001 }", 1 ] }
+{ "values": [ "time: { 14:40:41.001 }", 4 ] }
+{ "values": [ "time: { 22:12:51.999 }", 2 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.018.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.018.adm
new file mode 100644
index 0000000..8b9d5df
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.018.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 3 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
+{ "values": [ "datetime: { 2019-01-20T14:40:41.001 }", 4 ] }
+{ "values": [ "datetime: { 2020-02-20T01:40:41.001 }", 1 ] }
+{ "values": [ "datetime: { 2021-11-25T22:12:51.999 }", 2 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.019.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.019.adm
new file mode 100644
index 0000000..2cbfa87
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.019.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 3 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
+{ "values": [ "\"date\": { 2019-01-20 }", 4 ] }
+{ "values": [ "\"date\": { 2020-02-20 }", 1 ] }
+{ "values": [ "\"date\": { 2021-11-25 }", 2 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.020.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.020.adm
new file mode 100644
index 0000000..38633f5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.020.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 3 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
+{ "values": [ "time: { 01:40:41.001 }", 1 ] }
+{ "values": [ "time: { 14:40:41.001 }", 4 ] }
+{ "values": [ "time: { 22:12:51.999 }", 2 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.021.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.021.adm
new file mode 100644
index 0000000..8b9d5df
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.021.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 3 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
+{ "values": [ "datetime: { 2019-01-20T14:40:41.001 }", 4 ] }
+{ "values": [ "datetime: { 2020-02-20T01:40:41.001 }", 1 ] }
+{ "values": [ "datetime: { 2021-11-25T22:12:51.999 }", 2 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.022.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.022.adm
new file mode 100644
index 0000000..fe93409
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.022.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 1 ] }
+{ "values": [ null, 2 ] }
+{ "values": [ null, 3 ] }
+{ "values": [ null, 4 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.023.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.023.adm
new file mode 100644
index 0000000..2cbfa87
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.023.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 3 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
+{ "values": [ "\"date\": { 2019-01-20 }", 4 ] }
+{ "values": [ "\"date\": { 2020-02-20 }", 1 ] }
+{ "values": [ "\"date\": { 2021-11-25 }", 2 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.024.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.024.adm
new file mode 100644
index 0000000..38633f5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.024.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 3 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
+{ "values": [ "time: { 01:40:41.001 }", 1 ] }
+{ "values": [ "time: { 14:40:41.001 }", 4 ] }
+{ "values": [ "time: { 22:12:51.999 }", 2 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.025.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.025.adm
new file mode 100644
index 0000000..8b9d5df
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.025.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 3 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
+{ "values": [ "datetime: { 2019-01-20T14:40:41.001 }", 4 ] }
+{ "values": [ "datetime: { 2020-02-20T01:40:41.001 }", 1 ] }
+{ "values": [ "datetime: { 2021-11-25T22:12:51.999 }", 2 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.026.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.026.adm
new file mode 100644
index 0000000..2cbfa87
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.026.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 3 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
+{ "values": [ "\"date\": { 2019-01-20 }", 4 ] }
+{ "values": [ "\"date\": { 2020-02-20 }", 1 ] }
+{ "values": [ "\"date\": { 2021-11-25 }", 2 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.027.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.027.adm
new file mode 100644
index 0000000..38633f5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.027.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 3 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
+{ "values": [ "time: { 01:40:41.001 }", 1 ] }
+{ "values": [ "time: { 14:40:41.001 }", 4 ] }
+{ "values": [ "time: { 22:12:51.999 }", 2 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.028.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.028.adm
new file mode 100644
index 0000000..8b9d5df
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.028.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 3 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
+{ "values": [ "datetime: { 2019-01-20T14:40:41.001 }", 4 ] }
+{ "values": [ "datetime: { 2020-02-20T01:40:41.001 }", 1 ] }
+{ "values": [ "datetime: { 2021-11-25T22:12:51.999 }", 2 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.029.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.029.adm
new file mode 100644
index 0000000..fe93409
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.029.adm
@@ -0,0 +1,6 @@
+{ "values": [ null, 1 ] }
+{ "values": [ null, 2 ] }
+{ "values": [ null, 3 ] }
+{ "values": [ null, 4 ] }
+{ "values": [ null, 5 ] }
+{ "values": [ null, 6 ] }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.030.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.030.adm
new file mode 100644
index 0000000..0101b1d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.030.adm
@@ -0,0 +1,7 @@
+{ "IndexName": "ds3_o_idx_f_d", "Cast": { "Default": null } }
+{ "IndexName": "ds3_o_idx_f_d_fmt", "Cast": { "Default": null, "DataFormat": [ null, "MM/DD/YYYY", null ] } }
+{ "IndexName": "ds3_o_idx_f_dt", "Cast": { "Default": null } }
+{ "IndexName": "ds3_o_idx_f_dt_fmt", "Cast": { "Default": null, "DataFormat": [ "MM/DD/YYYY hh:mm:ss.nnna", null, null ] } }
+{ "IndexName": "ds3_o_idx_f_t", "Cast": { "Default": null } }
+{ "IndexName": "ds3_o_idx_f_t_fmt", "Cast": { "Default": null, "DataFormat": [ null, null, "hh:mm:ss.nnna" ] } }
+{ "IndexName": "ds3_o_idx_invalid_fmt", "Cast": { "Default": null, "DataFormat": [ null, "invalid_format", null ] } }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.031.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.031.adm
new file mode 100644
index 0000000..a8f3fda
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/ddl/index-cast-null/index-cast-null.031.adm
@@ -0,0 +1,7 @@
+{ "IndexName": "ds4_o_idx_f_d", "Cast": { "Default": null } }
+{ "IndexName": "ds4_o_idx_f_d_fmt", "Cast": { "Default": null, "DataFormat": [ null, "MM/DD/YYYY", null ] } }
+{ "IndexName": "ds4_o_idx_f_dt", "Cast": { "Default": null } }
+{ "IndexName": "ds4_o_idx_f_dt_fmt", "Cast": { "Default": null, "DataFormat": [ "MM/DD/YYYY hh:mm:ss.nnna", null, null ] } }
+{ "IndexName": "ds4_o_idx_f_t", "Cast": { "Default": null } }
+{ "IndexName": "ds4_o_idx_f_t_fmt", "Cast": { "Default": null, "DataFormat": [ null, null, "hh:mm:ss.nnna" ] } }
+{ "IndexName": "ds4_o_idx_invalid_fmt", "Cast": { "Default": null, "DataFormat": [ null, "invalid_format", null ] } }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.12.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.12.adm
new file mode 100644
index 0000000..107ba48
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.12.adm
@@ -0,0 +1,2 @@
+{ "id": 1, "f_dt_fmt": datetime("2020-02-20T01:40:41.001") }
+{ "id": 4, "f_dt_fmt": datetime("2019-01-20T14:40:41.001") }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.13.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.13.adm
new file mode 100644
index 0000000..bac020d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.13.adm
@@ -0,0 +1,2 @@
+{ "id": 1, "f_d_fmt": date("2020-02-20") }
+{ "id": 4, "f_d_fmt": date("2019-01-20") }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.14.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.14.adm
new file mode 100644
index 0000000..66960b4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.14.adm
@@ -0,0 +1,2 @@
+{ "id": 1, "f_t_fmt": time("01:40:41.001") }
+{ "id": 4, "f_t_fmt": time("14:40:41.001") }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.15.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.15.adm
new file mode 100644
index 0000000..efc9e6a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.15.adm
@@ -0,0 +1,2 @@
+{ "id": 1, "f_dt": datetime("2020-02-20T01:40:41.001") }
+{ "id": 4, "f_dt": datetime("2019-01-20T14:40:41.001") }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.16.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.16.adm
new file mode 100644
index 0000000..c0843a1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.16.adm
@@ -0,0 +1,2 @@
+{ "id": 1, "f_d": date("2020-02-20") }
+{ "id": 4, "f_d": date("2019-01-20") }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.17.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.17.adm
new file mode 100644
index 0000000..bae3de6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.17.adm
@@ -0,0 +1,2 @@
+{ "id": 1, "f_t": time("01:40:41.001") }
+{ "id": 4, "f_t": time("14:40:41.001") }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.18.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.18.adm
new file mode 100644
index 0000000..448a834
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/cast-default-null/cast-default-null.18.adm
@@ -0,0 +1,3 @@
+{ "v6f_dt": datetime("2019-01-20T14:40:41.001"), "v5f_dt_fmt": datetime("2019-01-20T14:40:41.001") }
+{ "v6f_dt": datetime("2020-02-20T01:40:41.001"), "v5f_dt_fmt": datetime("2020-02-20T01:40:41.001") }
+{ "v6f_dt": datetime("2021-11-25T22:12:51.999"), "v5f_dt_fmt": datetime("2021-11-25T22:12:51.999") }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index 7ea397e..cc0f92a 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -4322,6 +4322,7 @@
         <expected-error>Cast Default Null cannot be specified together with ENFORCED</expected-error>
         <expected-error>CAST is not allowed since field "[typed_f1]" is typed</expected-error>
         <expected-error>CAST is not allowed since field "[typed_f2]" is typed</expected-error>
+        <expected-error>Parameter invalid_date cannot be set</expected-error>
       </compilation-unit>
     </test-case>
   </test-group>
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/statement/CreateIndexStatement.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/statement/CreateIndexStatement.java
index 566838b..aabd76d 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/statement/CreateIndexStatement.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/statement/CreateIndexStatement.java
@@ -20,6 +20,7 @@
 
 import java.util.Collections;
 import java.util.List;
+import java.util.Map;
 import java.util.Objects;
 import java.util.stream.Collectors;
 
@@ -51,10 +52,12 @@
     private final String fullTextConfigName;
     private final OptionalBoolean excludeUnknownKey;
     private final OptionalBoolean castDefaultNull;
+    private final Map<String, String> castConfig;
 
     public CreateIndexStatement(DataverseName dataverseName, Identifier datasetName, Identifier indexName,
             IndexType indexType, List<IndexedElement> indexedElements, boolean enforced, int gramLength,
-            String fullTextConfigName, boolean ifNotExists, Boolean excludeUnknownKey, Boolean castDefaultNull) {
+            String fullTextConfigName, boolean ifNotExists, Boolean excludeUnknownKey, Boolean castDefaultNull,
+            Map<String, String> castConfig) {
         this.dataverseName = dataverseName;
         this.datasetName = Objects.requireNonNull(datasetName);
         this.indexName = Objects.requireNonNull(indexName);
@@ -66,6 +69,7 @@
         this.fullTextConfigName = fullTextConfigName;
         this.excludeUnknownKey = OptionalBoolean.ofNullable(excludeUnknownKey);
         this.castDefaultNull = OptionalBoolean.ofNullable(castDefaultNull);
+        this.castConfig = castConfig == null ? Collections.emptyMap() : castConfig;
     }
 
     public String getFullTextConfigName() {
@@ -120,6 +124,10 @@
         return this.ifNotExists;
     }
 
+    public Map<String, String> getCastConfig() {
+        return castConfig;
+    }
+
     @Override
     public Kind getKind() {
         return Statement.Kind.CREATE_INDEX;
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/util/ViewUtil.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/util/ViewUtil.java
index b833946..cb704b2 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/util/ViewUtil.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/util/ViewUtil.java
@@ -19,11 +19,12 @@
 
 package org.apache.asterix.lang.common.util;
 
+import static org.apache.asterix.metadata.utils.TypeUtil.getTemporalFormat;
+
 import java.io.StringReader;
 import java.util.ArrayList;
 import java.util.Collections;
 import java.util.List;
-import java.util.Map;
 
 import org.apache.asterix.common.exceptions.CompilationException;
 import org.apache.asterix.common.exceptions.ErrorCode;
@@ -38,7 +39,6 @@
 import org.apache.asterix.lang.common.expression.FieldAccessor;
 import org.apache.asterix.lang.common.expression.LiteralExpr;
 import org.apache.asterix.lang.common.expression.VariableExpr;
-import org.apache.asterix.lang.common.literal.NullLiteral;
 import org.apache.asterix.lang.common.literal.StringLiteral;
 import org.apache.asterix.lang.common.statement.ViewDecl;
 import org.apache.asterix.lang.common.struct.Identifier;
@@ -49,7 +49,6 @@
 import org.apache.asterix.om.types.ARecordType;
 import org.apache.asterix.om.types.ATypeTag;
 import org.apache.asterix.om.types.AUnionType;
-import org.apache.asterix.om.types.BuiltinType;
 import org.apache.asterix.om.types.IAType;
 import org.apache.hyracks.algebricks.common.utils.Triple;
 import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
@@ -58,10 +57,6 @@
 
 public final class ViewUtil {
 
-    public static final String DATETIME_PARAMETER_NAME = BuiltinType.ADATETIME.getTypeName();
-    public static final String DATE_PARAMETER_NAME = BuiltinType.ADATE.getTypeName();
-    public static final String TIME_PARAMETER_NAME = BuiltinType.ATIME.getTypeName();
-
     private ViewUtil() {
     }
 
@@ -144,40 +139,19 @@
             } else {
                 primeType = fieldType;
             }
-            if (TypeUtil.getTypeConstructorDefaultNull(primeType) == null) {
+            if (TypeUtil.getTypeConstructorDefaultNull(primeType, false) == null) {
                 throw new CompilationException(ErrorCode.COMPILATION_TYPE_UNSUPPORTED, sourceLoc, "view",
                         primeType.getTypeName());
             }
         }
     }
 
-    public static Map<String, String> validateViewConfiguration(Map<String, String> viewConfig,
-            SourceLocation sourceLoc) throws CompilationException {
-        if (viewConfig == null) {
-            return Collections.emptyMap();
-        }
-        for (Map.Entry<String, String> me : viewConfig.entrySet()) {
-            String name = me.getKey();
-            String value = me.getValue();
-            if (DATETIME_PARAMETER_NAME.equals(name) || DATE_PARAMETER_NAME.equals(name)
-                    || TIME_PARAMETER_NAME.equals(name)) {
-                if (value == null) {
-                    throw new CompilationException(ErrorCode.INVALID_REQ_PARAM_VAL, sourceLoc, name, value);
-                }
-            } else {
-                throw new CompilationException(ErrorCode.ILLEGAL_SET_PARAMETER, sourceLoc, name);
-            }
-        }
-        return viewConfig;
-    }
-
     public static Expression createTypeConvertExpression(Expression inExpr, IAType targetType,
             Triple<String, String, String> temporalDataFormat, DatasetFullyQualifiedName viewName,
             SourceLocation sourceLoc) throws CompilationException {
         String format = temporalDataFormat != null ? getTemporalFormat(targetType, temporalDataFormat) : null;
         boolean withFormat = format != null;
-        FunctionIdentifier constrFid = withFormat ? TypeUtil.getTypeConstructorWithFormatDefaultNull(targetType)
-                : TypeUtil.getTypeConstructorDefaultNull(targetType);
+        FunctionIdentifier constrFid = TypeUtil.getTypeConstructorDefaultNull(targetType, withFormat);
         if (constrFid == null) {
             throw new CompilationException(ErrorCode.COMPILATION_TYPE_UNSUPPORTED, sourceLoc, viewName.toString(),
                     targetType.getTypeName());
@@ -194,15 +168,6 @@
         return convertExpr;
     }
 
-    public static Expression createMissingToNullExpression(Expression inExpr, SourceLocation sourceLoc) {
-        List<Expression> missing2NullArgs = new ArrayList<>(2);
-        missing2NullArgs.add(inExpr);
-        missing2NullArgs.add(new LiteralExpr(NullLiteral.INSTANCE));
-        CallExpr missing2NullExpr = new CallExpr(new FunctionSignature(BuiltinFunctions.IF_MISSING), missing2NullArgs);
-        missing2NullExpr.setSourceLocation(sourceLoc);
-        return missing2NullExpr;
-    }
-
     public static Expression createNotIsNullExpression(Expression inExpr, SourceLocation sourceLoc) {
         List<Expression> isNullArgs = new ArrayList<>(1);
         isNullArgs.add(inExpr);
@@ -224,28 +189,4 @@
         return fa;
     }
 
-    public static String getTemporalFormat(IAType targetType, Triple<String, String, String> temporalFormatByType) {
-        switch (targetType.getTypeTag()) {
-            case DATETIME:
-                return temporalFormatByType.first;
-            case DATE:
-                return temporalFormatByType.second;
-            case TIME:
-                return temporalFormatByType.third;
-            default:
-                return null;
-        }
-    }
-
-    public static String getDatetimeFormat(Map<String, String> viewConfig) {
-        return viewConfig.get(DATETIME_PARAMETER_NAME);
-    }
-
-    public static String getDateFormat(Map<String, String> viewConfig) {
-        return viewConfig.get(DATE_PARAMETER_NAME);
-    }
-
-    public static String getTimeFormat(Map<String, String> viewConfig) {
-        return viewConfig.get(TIME_PARAMETER_NAME);
-    }
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index 728b240..395f63d 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -1198,7 +1198,9 @@
   String fullTextConfigName = null;
   Token startElementToken = null;
   Boolean excludeUnknown = null;
+  Pair<Map<String, String>, Boolean> castConfigDefaultNull;
   Boolean castDefaultNull = null;
+  Map<String, String> castConfig = null;
 }
 {
   (
@@ -1229,7 +1231,12 @@
     } <UNKNOWN> <KEY>
     )?
 
-    ( <CAST><LEFTPAREN><IDENTIFIER> { expectToken(DEFAULT); } <NULL><RIGHTPAREN> { castDefaultNull = true; })?
+    ( <CAST><LEFTPAREN> castConfigDefaultNull = CastDefaultNull() <RIGHTPAREN>
+      {
+        castConfig = castConfigDefaultNull.first;
+        castDefaultNull = castConfigDefaultNull.second;
+      }
+    )?
   )
   {
     IndexType indexType;
@@ -1245,7 +1252,7 @@
     }
     CreateIndexStatement stmt = new CreateIndexStatement(nameComponents.first, nameComponents.second,
       new Identifier(indexName), indexType, indexedElementList, enforced, gramLength, fullTextConfigName, ifNotExists,
-      excludeUnknown, castDefaultNull);
+      excludeUnknown, castDefaultNull, castConfig);
     return addSourceLocation(stmt, startStmtToken);
   }
 }
@@ -1374,7 +1381,8 @@
       indexName = "primary_idx_" + nameComponents.second;
     }
     CreateIndexStatement stmt = new CreateIndexStatement(nameComponents.first, nameComponents.second,
-      new Identifier(indexName), IndexType.BTREE, Collections.emptyList(), false, -1, null, ifNotExists, null, null);
+      new Identifier(indexName), IndexType.BTREE, Collections.emptyList(), false, -1, null, ifNotExists, null, null,
+      Collections.emptyMap());
     return addSourceLocation(stmt, startStmtToken);
   }
 }
@@ -1504,9 +1512,9 @@
   boolean ifNotExists = false;
   Token beginPos = null, endPos = null;
   Expression viewBodyExpr = null;
+  Pair<Map<String, String>, Boolean> viewConfigDefaultNull;
   Boolean defaultNull = null;
   Map<String, String> viewConfig = null;
-  String propertyName = null, propertyValue = null;
   Pair<List<Integer>, List<List<String>>> primaryKeyFields = null;
   Pair<List<Integer>, List<List<String>>> foreignKeyFields = null;
   Pair<DataverseName, Identifier> refNameComponents = null;
@@ -1519,16 +1527,11 @@
       (
         typeExpr = DatasetTypeSpecification()
         ifNotExists = IfNotExists()
-        <IDENTIFIER> { expectToken(DEFAULT); } <NULL> { defaultNull = true; }
-        (
-          LOOKAHEAD(2) <IDENTIFIER> { propertyName = token.image.toLowerCase(); } propertyValue = StringLiteral()
-          {
-            if (viewConfig == null) {
-              viewConfig = new HashMap<String, String>();
-            }
-            viewConfig.put(propertyName, propertyValue);
-          }
-        )*
+        viewConfigDefaultNull = CastDefaultNull()
+        {
+          viewConfig = viewConfigDefaultNull.first;
+          defaultNull = viewConfigDefaultNull.second;
+        }
         (
           <PRIMARY> <KEY> <LEFTPAREN> primaryKeyFields = PrimaryKeyFields() <RIGHTPAREN>
           <NOT> <ENFORCED>
@@ -1595,6 +1598,28 @@
   }
 }
 
+Pair<Map<String, String>, Boolean> CastDefaultNull() throws ParseException:
+{
+  Map<String, String> castConfig = null;
+  Boolean defaultNull = null;
+  String propertyName = null, propertyValue = null;
+}
+{
+  <IDENTIFIER> { expectToken(DEFAULT); } <NULL> { defaultNull = true; }
+    (
+      LOOKAHEAD(2) <IDENTIFIER> { propertyName = token.image.toLowerCase(); } propertyValue = StringLiteral()
+      {
+        if (castConfig == null) {
+          castConfig = new HashMap<String, String>();
+        }
+        castConfig.put(propertyName, propertyValue);
+      }
+    )*
+  {
+    return new Pair<Map<String, String>, Boolean>(castConfig, defaultNull);
+  }
+}
+
 CreateFunctionStatement CreateFunctionStatement(Token startStmtToken, boolean orReplace) throws ParseException:
 {
   CreateFunctionStatement stmt = null;
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/Index.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/Index.java
index 54b43db..b00ca82 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/Index.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/Index.java
@@ -84,9 +84,9 @@
             List<List<String>> keyFieldNames, List<Integer> keyFieldSourceIndicators, List<IAType> keyFieldTypes,
             boolean overrideKeyFieldTypes, boolean isEnforced, boolean isPrimaryIndex, int pendingOp,
             OptionalBoolean excludeUnknownKey) {
-        this(dataverseName, datasetName, indexName, indexType,
-                createSimpleIndexDetails(indexType, keyFieldNames, keyFieldSourceIndicators, keyFieldTypes,
-                        overrideKeyFieldTypes, excludeUnknownKey, OptionalBoolean.empty()),
+        this(dataverseName, datasetName,
+                indexName, indexType, createSimpleIndexDetails(indexType, keyFieldNames, keyFieldSourceIndicators,
+                        keyFieldTypes, overrideKeyFieldTypes, excludeUnknownKey),
                 isEnforced, isPrimaryIndex, pendingOp);
     }
 
@@ -95,7 +95,7 @@
             int pendingOp) {
         return new Index(dataverseName, datasetName,
                 datasetName, IndexType.BTREE, new ValueIndexDetails(keyFieldNames, keyFieldSourceIndicators,
-                        keyFieldTypes, false, OptionalBoolean.empty(), OptionalBoolean.empty()),
+                        keyFieldTypes, false, OptionalBoolean.empty(), OptionalBoolean.empty(), null, null, null),
                 false, true, pendingOp);
     }
 
@@ -337,15 +337,25 @@
 
         private final Boolean castDefaultNull;
 
+        private final String castDatetimeFormat;
+
+        private final String castDateFormat;
+
+        private final String castTimeFormat;
+
         public ValueIndexDetails(List<List<String>> keyFieldNames, List<Integer> keyFieldSourceIndicators,
                 List<IAType> keyFieldTypes, boolean overrideKeyFieldTypes, OptionalBoolean excludeUnknownKey,
-                OptionalBoolean castDefaultNull) {
+                OptionalBoolean castDefaultNull, String castDatetimeFormat, String castDateFormat,
+                String castTimeFormat) {
             this.keyFieldNames = keyFieldNames;
             this.keyFieldSourceIndicators = keyFieldSourceIndicators;
             this.keyFieldTypes = keyFieldTypes;
             this.overrideKeyFieldTypes = overrideKeyFieldTypes;
             this.excludeUnknownKey = excludeUnknownKey.isEmpty() ? null : excludeUnknownKey.get();
             this.castDefaultNull = castDefaultNull.isEmpty() ? null : castDefaultNull.get();
+            this.castDatetimeFormat = castDatetimeFormat;
+            this.castDateFormat = castDateFormat;
+            this.castTimeFormat = castTimeFormat;
         }
 
         @Override
@@ -373,6 +383,18 @@
             return OptionalBoolean.ofNullable(castDefaultNull);
         }
 
+        public String getCastDatetimeFormat() {
+            return castDatetimeFormat;
+        }
+
+        public String getCastDateFormat() {
+            return castDateFormat;
+        }
+
+        public String getCastTimeFormat() {
+            return castTimeFormat;
+        }
+
         @Override
         public boolean isOverridingKeyFieldTypes() {
             return overrideKeyFieldTypes;
@@ -508,14 +530,14 @@
     @Deprecated
     private static Index.IIndexDetails createSimpleIndexDetails(IndexType indexType, List<List<String>> keyFieldNames,
             List<Integer> keyFieldSourceIndicators, List<IAType> keyFieldTypes, boolean overrideKeyFieldTypes,
-            OptionalBoolean excludeUnknownKey, OptionalBoolean castDefaultNull) {
+            OptionalBoolean excludeUnknownKey) {
         if (indexType == null) {
             return null;
         }
         switch (Index.IndexCategory.of(indexType)) {
             case VALUE:
                 return new ValueIndexDetails(keyFieldNames, keyFieldSourceIndicators, keyFieldTypes,
-                        overrideKeyFieldTypes, excludeUnknownKey, castDefaultNull);
+                        overrideKeyFieldTypes, excludeUnknownKey, OptionalBoolean.empty(), null, null, null);
             case TEXT:
                 if (excludeUnknownKey.isPresent()) {
                     throw new IllegalArgumentException("excludeUnknownKey");
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/ViewDetails.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/ViewDetails.java
index dc60b9e..76c6b4d 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/ViewDetails.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entities/ViewDetails.java
@@ -19,6 +19,7 @@
 
 package org.apache.asterix.metadata.entities;
 
+import static org.apache.asterix.metadata.entitytupletranslators.AbstractTupleTranslator.writeDateTimeFormats;
 import static org.apache.asterix.om.types.AOrderedListType.FULL_OPEN_ORDEREDLIST_TYPE;
 
 import java.io.DataOutput;
@@ -286,28 +287,8 @@
         }
 
         // write field 'Format'
-        if (datetimeFormat != null || dateFormat != null || timeFormat != null) {
-            fieldName.reset();
-            aString.setValue(MetadataRecordTypes.FIELD_NAME_DATA_FORMAT);
-            stringSerde.serialize(aString, fieldName.getDataOutput());
-
-            OrderedListBuilder formatListBuilder = new OrderedListBuilder();
-            formatListBuilder.reset(FULL_OPEN_ORDEREDLIST_TYPE);
-            for (String format : new String[] { datetimeFormat, dateFormat, timeFormat }) {
-                itemValue.reset();
-                if (format == null) {
-                    nullSerde.serialize(ANull.NULL, itemValue.getDataOutput());
-                } else {
-                    aString.setValue(format);
-                    stringSerde.serialize(aString, itemValue.getDataOutput());
-                }
-                formatListBuilder.addItem(itemValue);
-            }
-            fieldValue.reset();
-            formatListBuilder.write(fieldValue.getDataOutput(), true);
-            viewRecordBuilder.addField(fieldName, fieldValue);
-        }
-
+        writeDateTimeFormats(datetimeFormat, dateFormat, timeFormat, viewRecordBuilder, aString, nullSerde, stringSerde,
+                fieldName, fieldValue, itemValue);
         viewRecordBuilder.write(out, true);
     }
 
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/AbstractTupleTranslator.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/AbstractTupleTranslator.java
index c4f1377..3f4e63e 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/AbstractTupleTranslator.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/AbstractTupleTranslator.java
@@ -19,17 +19,21 @@
 
 package org.apache.asterix.metadata.entitytupletranslators;
 
+import static org.apache.asterix.om.types.AOrderedListType.FULL_OPEN_ORDEREDLIST_TYPE;
+
 import java.io.ByteArrayInputStream;
 import java.io.DataInput;
 import java.io.DataInputStream;
 import java.util.Collection;
 
 import org.apache.asterix.builders.IARecordBuilder;
+import org.apache.asterix.builders.OrderedListBuilder;
 import org.apache.asterix.builders.RecordBuilder;
 import org.apache.asterix.common.metadata.DataverseName;
 import org.apache.asterix.formats.nontagged.SerializerDeserializerProvider;
 import org.apache.asterix.metadata.api.IMetadataEntityTupleTranslator;
 import org.apache.asterix.metadata.api.IMetadataIndex;
+import org.apache.asterix.metadata.bootstrap.MetadataRecordTypes;
 import org.apache.asterix.om.base.ABoolean;
 import org.apache.asterix.om.base.AInt32;
 import org.apache.asterix.om.base.AInt64;
@@ -38,7 +42,10 @@
 import org.apache.asterix.om.base.AOrderedList;
 import org.apache.asterix.om.base.ARecord;
 import org.apache.asterix.om.base.AString;
+import org.apache.asterix.om.base.IACursor;
+import org.apache.asterix.om.base.IAObject;
 import org.apache.asterix.om.types.ARecordType;
+import org.apache.asterix.om.types.ATypeTag;
 import org.apache.asterix.om.types.BuiltinType;
 import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
 import org.apache.hyracks.algebricks.common.utils.Triple;
@@ -143,4 +150,53 @@
         }
         return new Triple<>(dataverseName, second, third);
     }
+
+    protected static String getStringValue(IAObject obj) {
+        return obj.getType().getTypeTag() == ATypeTag.STRING ? ((AString) obj).getStringValue() : null;
+    }
+
+    protected static Triple<String, String, String> getDateTimeFormats(ARecord record) {
+        Triple<String, String, String> formats = new Triple<>(null, null, null);
+        int formatFieldPos = record.getType().getFieldIndex(MetadataRecordTypes.FIELD_NAME_DATA_FORMAT);
+        if (formatFieldPos >= 0) {
+            IACursor formatCursor = ((AOrderedList) record.getValueByPos(formatFieldPos)).getCursor();
+            if (formatCursor.next()) {
+                formats.first = getStringValue(formatCursor.get());
+                if (formatCursor.next()) {
+                    formats.second = getStringValue(formatCursor.get());
+                    if (formatCursor.next()) {
+                        formats.third = getStringValue(formatCursor.get());
+                    }
+                }
+            }
+        }
+        return formats;
+    }
+
+    public static void writeDateTimeFormats(String datetimeFormat, String dateFormat, String timeFormat,
+            IARecordBuilder recordBuilder, AMutableString aString, ISerializerDeserializer<ANull> nullSerde,
+            ISerializerDeserializer<AString> stringSerde, ArrayBackedValueStorage nameValue,
+            ArrayBackedValueStorage fieldValue, ArrayBackedValueStorage itemValue) throws HyracksDataException {
+        if (datetimeFormat != null || dateFormat != null || timeFormat != null) {
+            nameValue.reset();
+            aString.setValue(MetadataRecordTypes.FIELD_NAME_DATA_FORMAT);
+            stringSerde.serialize(aString, nameValue.getDataOutput());
+
+            OrderedListBuilder formatListBuilder = new OrderedListBuilder();
+            formatListBuilder.reset(FULL_OPEN_ORDEREDLIST_TYPE);
+            for (String format : new String[] { datetimeFormat, dateFormat, timeFormat }) {
+                itemValue.reset();
+                if (format == null) {
+                    nullSerde.serialize(ANull.NULL, itemValue.getDataOutput());
+                } else {
+                    aString.setValue(format);
+                    stringSerde.serialize(aString, itemValue.getDataOutput());
+                }
+                formatListBuilder.addItem(itemValue);
+            }
+            fieldValue.reset();
+            formatListBuilder.write(fieldValue.getDataOutput(), true);
+            recordBuilder.addField(nameValue, fieldValue);
+        }
+    }
 }
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/DatasetTupleTranslator.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/DatasetTupleTranslator.java
index cf4b714..0d05d46 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/DatasetTupleTranslator.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/DatasetTupleTranslator.java
@@ -343,23 +343,10 @@
                 }
 
                 // Format fields
-                String datetimeFormat = null, dateFormat = null, timeFormat = null;
-                int formatFieldPos =
-                        datasetDetailsRecord.getType().getFieldIndex(MetadataRecordTypes.FIELD_NAME_DATA_FORMAT);
-                if (formatFieldPos >= 0) {
-                    IACursor formatCursor =
-                            ((AOrderedList) datasetDetailsRecord.getValueByPos(formatFieldPos)).getCursor();
-                    if (formatCursor.next()) {
-                        datetimeFormat = getStringValue(formatCursor.get());
-                        if (formatCursor.next()) {
-                            dateFormat = getStringValue(formatCursor.get());
-                            if (formatCursor.next()) {
-                                timeFormat = getStringValue(formatCursor.get());
-                            }
-                        }
-                    }
-                }
-
+                Triple<String, String, String> dateTimeFormats = getDateTimeFormats(datasetDetailsRecord);
+                String datetimeFormat = dateTimeFormats.first;
+                String dateFormat = dateTimeFormats.second;
+                String timeFormat = dateTimeFormats.third;
                 datasetDetails = new ViewDetails(definition, dependencies, defaultNull, primaryKeyFields, foreignKeys,
                         datetimeFormat, dateFormat, timeFormat);
                 break;
@@ -434,10 +421,6 @@
         return CompressionManager.NONE;
     }
 
-    private static String getStringValue(IAObject obj) {
-        return obj.getType().getTypeTag() == ATypeTag.STRING ? ((AString) obj).getStringValue() : null;
-    }
-
     @Override
     public ITupleReference getTupleFromMetadataEntity(Dataset dataset) throws HyracksDataException {
         OrderedListBuilder listBuilder = new OrderedListBuilder();
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java
index 35c34e1..62d8119 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java
@@ -21,6 +21,7 @@
 
 import static org.apache.asterix.metadata.bootstrap.MetadataRecordTypes.FIELD_NAME_CAST;
 import static org.apache.asterix.metadata.bootstrap.MetadataRecordTypes.FIELD_NAME_DEFAULT;
+import static org.apache.asterix.om.types.AOrderedListType.FULL_OPEN_ORDEREDLIST_TYPE;
 
 import java.util.ArrayList;
 import java.util.Calendar;
@@ -390,6 +391,7 @@
 
                 OptionalBoolean excludeUnknownKey = OptionalBoolean.empty();
                 OptionalBoolean castDefaultNull = OptionalBoolean.empty();
+                String datetimeFormat = null, dateFormat = null, timeFormat = null;
                 boolean isBtreeIdx = indexType == IndexType.BTREE && !isPrimaryIndex && !keyFieldNames.isEmpty();
                 if (isBtreeIdx) {
                     // exclude unknown key value; default to always include unknowns for normal b-trees
@@ -412,13 +414,20 @@
                                 IAObject defaultVal = castRec.getValueByPos(defaultFieldPos);
                                 if (defaultVal.getType().getTypeTag() == ATypeTag.NULL) {
                                     castDefaultNull = OptionalBoolean.TRUE();
+
+                                    // Format fields
+                                    Triple<String, String, String> dateTimeFormats = getDateTimeFormats(castRec);
+                                    datetimeFormat = dateTimeFormats.first;
+                                    dateFormat = dateTimeFormats.second;
+                                    timeFormat = dateTimeFormats.third;
                                 }
                             }
                         }
                     }
                 }
                 indexDetails = new Index.ValueIndexDetails(keyFieldNames, keyFieldSourceIndicator, keyFieldTypes,
-                        isOverridingKeyTypes, excludeUnknownKey, castDefaultNull);
+                        isOverridingKeyTypes, excludeUnknownKey, castDefaultNull, datetimeFormat, dateFormat,
+                        timeFormat);
                 break;
             case TEXT:
                 keyFieldNames =
@@ -587,7 +596,7 @@
         writeEnforced(index);
         writeSearchKeySourceIndicator(index);
         writeExcludeUnknownKey(index);
-        writeCastDefaultNull(index);
+        writeCast(index);
     }
 
     private void writeComplexSearchKeys(Index.ArrayIndexDetails indexDetails) throws HyracksDataException {
@@ -816,12 +825,13 @@
         }
     }
 
-    private void writeCastDefaultNull(Index index) throws HyracksDataException {
+    private void writeCast(Index index) throws HyracksDataException {
         if (index.getIndexType() == IndexType.BTREE && !index.isPrimaryIndex() && !index.isPrimaryKeyIndex()) {
-            boolean defaultNull =
-                    ((Index.ValueIndexDetails) index.getIndexDetails()).getCastDefaultNull().getOrElse(false);
-            // "Default" field
+            Index.ValueIndexDetails indexDetails = (Index.ValueIndexDetails) index.getIndexDetails();
+            boolean defaultNull = indexDetails.getCastDefaultNull().getOrElse(false);
+            // write record field 'Cast'
             if (defaultNull) {
+                // write field 'Default'
                 castRecordBuilder.reset(RecordUtil.FULLY_OPEN_RECORD_TYPE);
                 fieldValue.reset();
                 nameValue.reset();
@@ -830,6 +840,13 @@
                 nullSerde.serialize(ANull.NULL, fieldValue.getDataOutput());
                 castRecordBuilder.addField(nameValue, fieldValue);
 
+                // write field 'Format'
+                String datetimeFormat = indexDetails.getCastDatetimeFormat();
+                String dateFormat = indexDetails.getCastDateFormat();
+                String timeFormat = indexDetails.getCastTimeFormat();
+                writeDateTimeFormats(datetimeFormat, dateFormat, timeFormat, castRecordBuilder, aString, nullSerde,
+                        stringSerde, nameValue, fieldValue, itemValue);
+
                 nameValue.reset();
                 fieldValue.reset();
                 aString.setValue(FIELD_NAME_CAST);
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/IndexUtil.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/IndexUtil.java
index 24f2249..215a295 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/IndexUtil.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/IndexUtil.java
@@ -33,8 +33,12 @@
 import org.apache.asterix.metadata.entities.Dataset;
 import org.apache.asterix.metadata.entities.Index;
 import org.apache.asterix.metadata.entities.InternalDatasetDetails;
+import org.apache.asterix.om.types.IAType;
 import org.apache.asterix.runtime.job.listener.JobEventListenerFactory;
 import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
+import org.apache.hyracks.algebricks.common.utils.Pair;
+import org.apache.hyracks.algebricks.common.utils.Triple;
+import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
 import org.apache.hyracks.api.dataflow.value.ITypeTraits;
 import org.apache.hyracks.api.exceptions.SourceLocation;
 import org.apache.hyracks.api.job.IJobletEventListenerFactory;
@@ -183,4 +187,31 @@
         return Index.IndexCategory.of(index.getIndexType()) == Index.IndexCategory.VALUE
                 && ((Index.ValueIndexDetails) index.getIndexDetails()).getCastDefaultNull().getOrElse(false);
     }
+
+    public static Pair<FunctionIdentifier, String> getTypeConstructorDefaultNull(Index index, IAType type,
+            SourceLocation srcLoc) throws CompilationException {
+        Triple<String, String, String> temporalFormats = getTemporalFormats(index);
+        String format = temporalFormats != null ? TypeUtil.getTemporalFormat(type, temporalFormats) : null;
+        boolean withFormat = format != null;
+        FunctionIdentifier typeConstructorFun = TypeUtil.getTypeConstructorDefaultNull(type, withFormat);
+        if (typeConstructorFun == null) {
+            throw new CompilationException(ErrorCode.COMPILATION_TYPE_UNSUPPORTED, srcLoc, "index", type.getTypeName());
+        }
+        return new Pair<>(typeConstructorFun, format);
+    }
+
+    private static Triple<String, String, String> getTemporalFormats(Index index) {
+        if (Index.IndexCategory.of(index.getIndexType()) != Index.IndexCategory.VALUE) {
+            return null;
+        }
+        Index.ValueIndexDetails indexDetails = (Index.ValueIndexDetails) index.getIndexDetails();
+        String datetimeFormat = indexDetails.getCastDatetimeFormat();
+        String dateFormat = indexDetails.getCastDateFormat();
+        String timeFormat = indexDetails.getCastTimeFormat();
+        if (datetimeFormat != null || dateFormat != null || timeFormat != null) {
+            return new Triple<>(datetimeFormat, dateFormat, timeFormat);
+        } else {
+            return null;
+        }
+    }
 }
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryBTreeOperationsHelper.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryBTreeOperationsHelper.java
index 50cec37..cdcfc0b 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryBTreeOperationsHelper.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryBTreeOperationsHelper.java
@@ -21,8 +21,6 @@
 import java.util.List;
 
 import org.apache.asterix.common.config.DatasetConfig.DatasetType;
-import org.apache.asterix.common.exceptions.CompilationException;
-import org.apache.asterix.common.exceptions.ErrorCode;
 import org.apache.asterix.common.utils.StorageConstants;
 import org.apache.asterix.external.indexing.IndexingConstants;
 import org.apache.asterix.external.operators.ExternalScanOperatorDescriptor;
@@ -31,6 +29,8 @@
 import org.apache.asterix.metadata.entities.Dataset;
 import org.apache.asterix.metadata.entities.Index;
 import org.apache.asterix.metadata.entities.InternalDatasetDetails;
+import org.apache.asterix.om.base.AString;
+import org.apache.asterix.om.constants.AsterixConstantValue;
 import org.apache.asterix.om.functions.IFunctionDescriptor;
 import org.apache.asterix.om.functions.IFunctionManager;
 import org.apache.asterix.om.typecomputer.impl.TypeComputeUtils;
@@ -313,7 +313,7 @@
 
         IScalarEvaluatorFactory castFieldEvalFactory;
         if (IndexUtil.castDefaultNull(index)) {
-            castFieldEvalFactory = createConstructorFunction(funManger, fieldEvalFactory, fieldType);
+            castFieldEvalFactory = createConstructorFunction(funManger, dataFormat, fieldEvalFactory, fieldType);
         } else if (index.isEnforced()) {
             IScalarEvaluatorFactory[] castArg = new IScalarEvaluatorFactory[] { fieldEvalFactory };
             castFieldEvalFactory =
@@ -326,17 +326,24 @@
         return castFieldEvalFactory;
     }
 
-    private IScalarEvaluatorFactory createConstructorFunction(IFunctionManager funManager,
+    private IScalarEvaluatorFactory createConstructorFunction(IFunctionManager funManager, IDataFormat dataFormat,
             IScalarEvaluatorFactory fieldEvalFactory, IAType fieldType) throws AlgebricksException {
-        FunctionIdentifier typeConstructorFun =
-                TypeUtil.getTypeConstructorDefaultNull(TypeComputeUtils.getActualType(fieldType));
-        if (typeConstructorFun == null) {
-            throw new CompilationException(ErrorCode.COMPILATION_TYPE_UNSUPPORTED, sourceLoc, "index",
-                    fieldType.getTypeName());
-        }
+        IAType targetType = TypeComputeUtils.getActualType(fieldType);
+        Pair<FunctionIdentifier, String> constructorWithFmt =
+                IndexUtil.getTypeConstructorDefaultNull(index, targetType, sourceLoc);
+        FunctionIdentifier typeConstructorFun = constructorWithFmt.first;
         IFunctionDescriptor typeConstructor = funManager.lookupFunction(typeConstructorFun, sourceLoc);
+        IScalarEvaluatorFactory[] args;
+        // add the format argument if specified
+        if (constructorWithFmt.second != null) {
+            IScalarEvaluatorFactory fmtEvalFactory =
+                    dataFormat.getConstantEvalFactory(new AsterixConstantValue(new AString(constructorWithFmt.second)));
+            args = new IScalarEvaluatorFactory[] { fieldEvalFactory, fmtEvalFactory };
+        } else {
+            args = new IScalarEvaluatorFactory[] { fieldEvalFactory };
+        }
         typeConstructor.setSourceLocation(sourceLoc);
-        return typeConstructor.createEvaluatorFactory(new IScalarEvaluatorFactory[] { fieldEvalFactory });
+        return typeConstructor.createEvaluatorFactory(args);
     }
 
     private int[] createFieldPermutationForBulkLoadOp(int numSecondaryKeyFields) {
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/TypeUtil.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/TypeUtil.java
index 8a6cfe2..5900c8a 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/TypeUtil.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/TypeUtil.java
@@ -39,14 +39,20 @@
 import org.apache.asterix.om.types.ARecordType;
 import org.apache.asterix.om.types.ATypeTag;
 import org.apache.asterix.om.types.AUnionType;
+import org.apache.asterix.om.types.BuiltinType;
 import org.apache.asterix.om.types.IAType;
 import org.apache.asterix.om.types.TypeSignature;
 import org.apache.asterix.om.types.hierachy.ATypeHierarchy;
+import org.apache.asterix.om.utils.ConstantExpressionUtil;
 import org.apache.commons.lang3.ArrayUtils;
+import org.apache.commons.lang3.mutable.Mutable;
 import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
 import org.apache.hyracks.algebricks.common.utils.Pair;
 import org.apache.hyracks.algebricks.common.utils.Triple;
+import org.apache.hyracks.algebricks.core.algebra.base.ILogicalExpression;
+import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
+import org.apache.hyracks.api.exceptions.SourceLocation;
 
 /**
  * Provider utility methods for data types
@@ -59,15 +65,22 @@
 
     private static final String FUNCTION_INLINE_TYPE_PREFIX = "$f$t$";
 
+    private static final String DATETIME_PARAMETER_NAME = BuiltinType.ADATETIME.getTypeName();
+
+    private static final String DATE_PARAMETER_NAME = BuiltinType.ADATE.getTypeName();
+
+    private static final String TIME_PARAMETER_NAME = BuiltinType.ATIME.getTypeName();
+
     private TypeUtil() {
     }
 
     /**
      * @param type type
+     * @param withFormat true when needing a constructor with format
      *
      * @return a type constructor that produces NULL for MISSING input
      */
-    public static FunctionIdentifier getTypeConstructorDefaultNull(IAType type) {
+    public static FunctionIdentifier getTypeConstructorDefaultNull(IAType type, boolean withFormat) {
         switch (type.getTypeTag()) {
             case TINYINT:
                 return BuiltinFunctions.INT8_DEFAULT_NULL_CONSTRUCTOR;
@@ -86,11 +99,14 @@
             case STRING:
                 return BuiltinFunctions.STRING_DEFAULT_NULL_CONSTRUCTOR;
             case DATE:
-                return BuiltinFunctions.DATE_DEFAULT_NULL_CONSTRUCTOR;
+                return withFormat ? BuiltinFunctions.DATE_DEFAULT_NULL_CONSTRUCTOR_WITH_FORMAT
+                        : BuiltinFunctions.DATE_DEFAULT_NULL_CONSTRUCTOR;
             case TIME:
-                return BuiltinFunctions.TIME_DEFAULT_NULL_CONSTRUCTOR;
+                return withFormat ? BuiltinFunctions.TIME_DEFAULT_NULL_CONSTRUCTOR_WITH_FORMAT
+                        : BuiltinFunctions.TIME_DEFAULT_NULL_CONSTRUCTOR;
             case DATETIME:
-                return BuiltinFunctions.DATETIME_DEFAULT_NULL_CONSTRUCTOR;
+                return withFormat ? BuiltinFunctions.DATETIME_DEFAULT_NULL_CONSTRUCTOR_WITH_FORMAT
+                        : BuiltinFunctions.DATETIME_DEFAULT_NULL_CONSTRUCTOR;
             case YEARMONTHDURATION:
                 return BuiltinFunctions.YEAR_MONTH_DURATION_DEFAULT_NULL_CONSTRUCTOR;
             case DAYTIMEDURATION:
@@ -106,24 +122,64 @@
         }
     }
 
-    /**
-     * @param type type
-     *
-     * @return a type constructor that produces NULL for MISSING input
-     */
-    public static FunctionIdentifier getTypeConstructorWithFormatDefaultNull(IAType type) {
-        switch (type.getTypeTag()) {
-            case DATE:
-                return BuiltinFunctions.DATE_DEFAULT_NULL_CONSTRUCTOR_WITH_FORMAT;
-            case TIME:
-                return BuiltinFunctions.TIME_DEFAULT_NULL_CONSTRUCTOR_WITH_FORMAT;
+    public static Map<String, String> validateConfiguration(Map<String, String> config, SourceLocation sourceLoc)
+            throws CompilationException {
+        if (config == null) {
+            return Collections.emptyMap();
+        }
+        for (Map.Entry<String, String> me : config.entrySet()) {
+            String name = me.getKey();
+            String value = me.getValue();
+            if (DATETIME_PARAMETER_NAME.equals(name) || DATE_PARAMETER_NAME.equals(name)
+                    || TIME_PARAMETER_NAME.equals(name)) {
+                if (value == null) {
+                    throw new CompilationException(ErrorCode.INVALID_REQ_PARAM_VAL, sourceLoc, name, "null");
+                }
+            } else {
+                throw new CompilationException(ErrorCode.ILLEGAL_SET_PARAMETER, sourceLoc, name);
+            }
+        }
+        return config;
+    }
+
+    public static String getDatetimeFormat(Map<String, String> config) {
+        return config.get(DATETIME_PARAMETER_NAME);
+    }
+
+    public static String getDateFormat(Map<String, String> config) {
+        return config.get(DATE_PARAMETER_NAME);
+    }
+
+    public static String getTimeFormat(Map<String, String> config) {
+        return config.get(TIME_PARAMETER_NAME);
+    }
+
+    public static String getTemporalFormat(IAType targetType, Triple<String, String, String> temporalFormatByType) {
+        switch (targetType.getTypeTag()) {
             case DATETIME:
-                return BuiltinFunctions.DATETIME_DEFAULT_NULL_CONSTRUCTOR_WITH_FORMAT;
+                return temporalFormatByType.first;
+            case DATE:
+                return temporalFormatByType.second;
+            case TIME:
+                return temporalFormatByType.third;
             default:
                 return null;
         }
     }
 
+    public static String getTemporalFormatArg(AbstractFunctionCallExpression funExpr) {
+        FunctionIdentifier funId = funExpr.getFunctionIdentifier();
+        if (BuiltinFunctions.DATE_DEFAULT_NULL_CONSTRUCTOR_WITH_FORMAT.equals(funId)
+                || BuiltinFunctions.TIME_DEFAULT_NULL_CONSTRUCTOR_WITH_FORMAT.equals(funId)
+                || BuiltinFunctions.DATETIME_DEFAULT_NULL_CONSTRUCTOR_WITH_FORMAT.equals(funId)) {
+            List<Mutable<ILogicalExpression>> arguments = funExpr.getArguments();
+            if (arguments.size() > 1) {
+                return ConstantExpressionUtil.getStringConstant(arguments.get(1).getValue());
+            }
+        }
+        return null;
+    }
+
     private static class EnforcedTypeBuilder {
         private final Deque<Triple<IAType, String, Boolean>> typeStack = new ArrayDeque<>();
         private List<Boolean> keyUnnestFlags;