How to consider null date if date is '01/01/0001'

[saivina2920]

How to consider null date if date is '01/01/0001'

Date(Floor([EMP_RELIEVE_DATE1]), 'DD/MM/YYYY') as [EMP_RELIEVE_DATE],
if(len([EMP_RELIEVE_DATE1])=0,1,0) as HasNullDate
Inline [
DOC_NO,EMP_STATUS,EMP_COUNTRY,EMP_STATE,EMP_RESIGN_DATE1,EMP_RELIEVE_DATE1
DOC-001,Current,Australia,Sydney,14/08/2020,
DOC-001,Current,Australia,Melbourne,15/08/2020,15/09/2020
DOC-002,Current,India,Gujarat,20/08/2020,20/09/2020
DOC-003,Current,India,Gujarat,25/08/2020,01/01/0001
DOC-004,Current,India,Gujarat,12/01/2021,02/02/2021

[edwin · Accepted solution]

try this as a calculated dimension:

[QFabian · Accepted solution]

Hi @saivina2920  please try this , because valid date stars in 1/1/1900 with num() value = 1, so if the date hasnt correct vlue, is going to show a negative result:

num('01/01/0001') = -693593

=if(num(EMP_RELIEVE_DATE1)>0,0,1)

[Chanty4u]

or you can do like below

if(Yourdate='01/01/0001','No date found', Yourdate) as Nulldate

or

if(not isNum(Yourdate),'Nulldate',date(yourdate,'DD/MM/YYYY')) as Date

[Waïl_Riachi_Sanofi]

Hello @Chanty4u ;

They tell me "missing right parenthesis"