Converting Date Fields in Character Format to Date Format

Brian Element

Forums

Hello Group Members,
 
Date fields in an IDEA database may appear within IDEA post import in Character format. This precludes Date based analytics since the field is in a Character format.
 
There are two ways we can convert the Date field in a Character format to a Date format.
 
One - Modify the existing field ~ change the field type from Character to Date and enter a suitable mask to mimic the Date. For example 30-JAN-19 is to be masked as DD-MMM-YY
 
Two - Append a new field ~ append a virtual date field with the criteria @ctod(Field Name, "DD-MMM-YY") in case the dates are appearing as 30-JAN-19. 
 
Once the field is appearing in a date format the much required Date based analytics can be done like Aging and more.
 
Best Regards
 
Group Admin Team

Equipmentman1662 Thu, 01/23/2020 - 10:22

I am assuming this is a fairly common problem...
What is the most efficient way to utilize the @Ctod function, when you have two different masks... Example:
The field "Transactions Date" has a wide array of ranges from January through December, but the masks are different. 1/2/2019 (8 characters) and 12/12/2019 (10 Characters). 

Brian Element Thu, 01/23/2020 - 11:02

If you have multiple date formats you can do something like this in the equation editor where you are looking for each different format and then changing the @Ctod based on the format.  

@CompIf(@Mid(DATE, 2, 1) = "/"  .AND. @Mid(DATE, 4, 1) = "/", @Ctod(DATE, "D/M/YYYY"), @Mid(DATE, 3, 1) = "/"  .AND. @Mid(DATE, 5, 1) = "/", @Ctod(DATE, "DD/M/YYYY"), @Mid(DATE, 2, 1) = "/"  .AND. @Mid(DATE, 5, 1) = "/", @Ctod(DATE, "D/MM/YYYY"),1, @Ctod(DATE, "DD/MM/YYYY"))

As you can see I am using the locations of the / to figure out the formatting.

Reece Tue, 02/11/2020 - 06:05

I'm trying to convert a date field from a character type to a date type by using the following: @ctod(RECEIPT_DATE,"DD/MM/YYYY")
The field name is correct and the dates are in the format: 07/01/2019, 26/11/2018 etc.
 
When validating, there are no errors, however, when I press OK to execuate the change, I get the following error "A valid date mask is required for this field". I believe all the dates in this field have the same format.
Any idea what could be the cause?