Converting Date Fields in Character Format to Date Format

6 posts / 0 new
Last post
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57
Converting Date Fields in Character Format to Date Format
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
Offline
Joined: 05/23/2018 - 16:46

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's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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
Offline
Joined: 02/06/2020 - 07:18

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?

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

That looks fine, any chance you can share your database or part of it that is causing the error so I can try it out.

Reece
Offline
Joined: 02/06/2020 - 07:18

Thanks for the reply, Brian.
I was just speaking to someone within the company who advised me to simply put DD/MM/YYYY and that has worked wonders.