Converting blank dates

3 posts / 0 new
Last post
ericardo79
Offline
Joined: 03/07/2019 - 21:58
Converting blank dates

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
Offline
Joined: 07/31/2012 - 13:20

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

ericardo79
Offline
Joined: 03/07/2019 - 21:58

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