Converting blank dates
ericardo79
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.
Hello,
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