ACL Date Function – CTOD () Function

Welcome to the latest post in the long running series of ‘Audit Command Language Tutorials for Beginners’. We are making very steady progress with the coverage of topics in these posts. We have so far covered the most frequently used ACL character functions and are currently in the early stages of going through the most common ACL date functions.

In this post, we are going to cover the most commonly used date function. The CTOD  function. The  CTOD function essentially stands for ‘Convert to Date’. This function is used, when the date field in the raw data in imported as a text in a standard date format. in such a case, this function converts the raw field into an ACL date format. There is a list of expected formats that are allowed in the raw data, which can be converted to ACL date format. You can refer to these in the ACL help section.

Syntax: CTOD(string/number <,format>)

Example in a script to create a new field using CTOD Function

DELETE FIELD TEST_Field2 OK
DEFINE FIELD TEST_Field2 COMPUTED AS
CTOD(Test_Field,”MM/DD/YYYY”)

Example in a workspace to create a new field using CTOD Function

TEST_Field2 Computed
CTOD(Test_Field,”MM/DD/YYYY”)

The result shown in the screenshot below is a continuation of the example used in the last post, where we converted a Date field to string (‘Date Hired’ to ‘Test_Field’). The same string/text field has been converted back to a date field (Test_field’ to ‘Test_Field2’)  as shown in the screenshot below:

The example used in this post touches upon a couple concepts related to conversion of one data type to another in the Audit Command Language and how they result in creation of new fields in a table. This would give beginner data analysts how more complex scenarios can develop in a real work scenario where data sets are very large. See the video demonstrating the use of the CTOD function below:

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go through the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.