Converting blank dates

ericardo79

Forums

Hi,
I have an employee masterfile where the Termination Date is in character. I created a new field with @ctod on the date but it gave me an error on blank dates, ie non-terminated employees.
As a resolution, I used @if to specify if the termination date field .NOT. isblank, then convert to date. However, I am not able to specify a blank date at the end of the @if function. What should I type to indicate as NULL date?
Thanks.

Steven Luciani Fri, 04/12/2019 - 09:49

Hello,

The answer I'm giving is dependant on original date formatting. For my example the col1 field was imported as character data that should have been dates originallly formatted as yyyy-mm-dd but due to blank records was imported as character data 2019-01-30 and blank records where the orginal data record was empty. If you have a different original format you just need to tweak the parts of the equation below that are between the quotation marks. See below

yyyy-mm-dd

@ctod(@if(@isblank(col1)=1,"0000-00-00",COL1),"yyyy-mm-dd")

 

mm/dd/yyyy

@ctod(@if(@isblank(col1)=1,"00/00/0000",COL1),"mm/dd/yyyy")

 

mm/dd/yy

@ctod(@if(@isblank(col1)=1,"00/00/00",COL1),"mm/dd/yy")

You create a virtual date or regular date field and your output for the blank imported records will be 00/00/0000 or some version of that based on how Windows displays dates on you computer.

Cheers,

Steven

talebi Fri, 01/14/2022 - 18:33

Thanks

ericardo79 Sun, 04/14/2019 - 07:47

Thanks Steven!
Good idea to set the date as 00/00/00 and covert to date field after.

osaajah Sun, 04/21/2019 - 12:37

Actually only character field can has blank value or empty. Other fields must be set to zero to represent blank or empty.  And it can be hard to interpret real zero or blank values.