Assign 'null' to date column

Comments

7 comments

  • Official comment
    Brian Junio

    I believe the command you're looking for is:
    ASDATE(null)

    The "null" is the keyword for null values and in order to cast it to a Date type object, use a function such as ASDATE which exclusively returns Date type nulls.

    Comment actions Permalink
  • Eduardo Martinez

    I am getting this error, when I use that function:

    Caused by: java.lang.IllegalArgumentException: Unable to parse the date: null

     

     

    0
    Comment actions Permalink
  • Joel Stewart

    Eduardo, could you please share the rest of the stacktrace? It's difficult to understand the situation just from the single error message.

     

    0
    Comment actions Permalink
  • Eduardo Martinez

    I am using a formula  =ASDATE("null") to create a null date for my column END_DT. and I am getting en error with this information:

    ERROR [2017-01-31 21:34:21.212] [JobScheduler thread-1] (JobScheduler.java:829) - Job 15679 failed with exception.
    java.lang.RuntimeException: #L2_Mapping_ETL1!End_DT: Unable to parse the date: null
    	at datameer.dap.common.workbook.context.WorkbookContextImpl.assertNoErrors(WorkbookContextImpl.java:715)
    	at datameer.dap.common.job.WorkbookJob.checkForValidness(WorkbookJob.java:187)
    	at datameer.dap.common.job.WorkbookJob.compileWorkbook(WorkbookJob.java:145)
    	at datameer.dap.common.job.WorkbookJob.registerJobOperations(WorkbookJob.java:246)
    	at datameer.dap.common.job.DatameerJob.createExecutionPlan(DatameerJob.java:78)
    	at datameer.dap.common.job.DasJobCallable.call(DasJobCallable.java:116)
    	at datameer.dap.common.job.DasJobCallable.call(DasJobCallable.java:78)
    	at datameer.dap.conductor.job.JobSchedulerJob$2.call(JobSchedulerJob.java:116)
    	at datameer.dap.conductor.job.JobSchedulerJob$2.call(JobSchedulerJob.java:101)
    	at datameer.dap.common.security.DatameerSecurityService.runAsUser(DatameerSecurityService.java:109)
    	at datameer.dap.conductor.job.JobSchedulerJob.call(JobSchedulerJob.java:101)
    	at datameer.dap.conductor.job.JobSchedulerJob.call(JobSchedulerJob.java:41)
    	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    	at java.lang.Thread.run(Thread.java:745)
    Caused by: datameer.dap.common.formula.SheetException: #L2_Mapping_ETL1!End_DT: Unable to parse the date: null
    	at datameer.dap.common.formula.SheetException.create(SheetException.java:42)
    	at datameer.dap.common.formula.FormulaSheetModel.registerColumnError(FormulaSheetModel.java:256)
    	at datameer.dap.common.formula.FormulaSheetModel.initColumnBundles(FormulaSheetModel.java:292)
    	at datameer.dap.common.formula.FormulaSheetModel.<init>(FormulaSheetModel.java:189)
    	at datameer.dap.common.sheet.FormulaSheetType.createSheetModel(FormulaSheetType.java:61)
    	at datameer.dap.common.workbook.context.WorkbookContextImpl.createSheetModel(WorkbookContextImpl.java:274)
    	at datameer.dap.common.workbook.context.WorkbookContextImpl.registerSheetModel(WorkbookContextImpl.java:238)
    	at datameer.dap.common.workbook.context.WorkbookContextImpl.register(WorkbookContextImpl.java:205)
    	at datameer.dap.common.workbook.context.WorkbookContextImpl.init(WorkbookContextImpl.java:127)
    	at datameer.dap.common.workbook.context.RootWorkbookContext.<init>(RootWorkbookContext.java:31)
    	at datameer.dap.common.workbook.context.RootWorkbookContext.create(RootWorkbookContext.java:24)
    	at datameer.dap.common.entity.WorkbookConfigurationImpl.createContext(WorkbookConfigurationImpl.java:594)
    	at datameer.dap.common.entity.WorkbookConfigurationImpl.createContext(WorkbookConfigurationImpl.java:582)
    	at datameer.dap.common.job.WorkbookJob.compileWorkbook(WorkbookJob.java:143)
    	... 13 more
    Caused by: java.lang.IllegalArgumentException: Unable to parse the date: null
    	at datameer.das.functions.dateandtime.AsDateFunction$DetectingComputor.detectFormatAndParse(AsDateFunction.java:152)
    	at datameer.das.functions.dateandtime.AsDateFunction$DetectingComputor.compute(AsDateFunction.java:161)
    	at datameer.dap.common.formula.lazy.EvalSequence$ValueComputorSequence$1.computeValue(EvalSequence.java:395)
    	at datameer.dap.common.formula.lazy.SingleEvalSequence.currentValue(SingleEvalSequence.java:31)
    	at datameer.dap.common.formula.lazy.ArgumentsEvalSequence.currentValue(ArgumentsEvalSequence.java:141)
    	at datameer.dap.common.formula.lazy.EvalSequence.currentIsError(EvalSequence.java:46)
    	at datameer.dap.common.formula.lazy.EvalSequence.onlyElement(EvalSequence.java:313)
    	at datameer.dap.common.formula.ExpressionWithArguments.simplify(ExpressionWithArguments.java:244)
    	at datameer.dap.common.formula.FormulaSheetModel.initColumnBundles(FormulaSheetModel.java:290)

     

    0
    Comment actions Permalink
  • Joel Stewart

    Thanks Eduardo! The formula appears to include quote characters around the value null. As a result, Datameer is trying to interpret this value as a STRING and not as the Java NULL value. Removing the quotes should resolve this issue. 

    Here's the adjusted formula: ASDATE(null)

    Hope this helps! 

    0
    Comment actions Permalink
  • Kunal Gaurav

    Hello Brian,

     

    I am trying to use the below formula for my requirement which says, if the value is BLANK or NULL, then make it BLANK else keep the date.

    Can you please let me know, where am I using the wrong formula as the error says 'Syntax Error'.

    IF(ISNULL(#Self_Validation_xlsx_Sheet3!A);ASDATE(NULL);#Self_Validation_xlsx_Sheet3!A)

     

    Thanks

    Kunal

    0
    Comment actions Permalink
  • Konsta Danyliuk

    Hello Kunal.
    Please try to use ASDATE(null) instead of ASDATE(NULL). I've tested at my Datameer-7.4.3 lab and ASDATE(null) works fine, while ASDATE(NULL) returns syntax error.

    IF(ISNULL(#Self_Validation_xlsx_Sheet3!A);ASDATE(null);#Self_Validation_xlsx_Sheet3!A)
    0
    Comment actions Permalink

Please sign in to leave a comment.