Announcements
cancel
Showing results for
Did you mean:
Creator

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

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

Labels (1)
• ### Scripting

2 Solutions

Accepted Solutions
Master

try this as a calculated dimension:

Specialist III

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)

QFabian
4 Replies
Master

try this as a calculated dimension:

Specialist III

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)

QFabian
MVP

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

Creator

Hello @Chanty4u ;

They tell me "missing right parenthesis"

Community Browser