ACL Date Functions – Date () Function

Welcome to the latest post in the series ‘Audit Command Language Tutorial for Beginners’. We are starting off a series of posts to discuss the most commonly used Date Functions  in the Audit Command Language. In the next six to seven posts we will explore the most commonly used date functions in the professional arena to perform work on industry level projects.

In this post, we start off with the function Date. In the most cases, the raw data files are imported with all fields mapped as ‘text’ or ‘ASCII’ data types in order to ensure that all the data points provided by the clients or stakeholders is captured correctly so that no data points is left out for analysis. ACL functions like Date, Value, String etc. are used to convert the raw data points to required formats during data transformation. The Date function is used to convert data read as ‘Date’ into a string or ASCII format.

Syntax:DATE( <date/datetime> <,format>)

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

DELETE FIELD TEST_FIELD OK
DEFINE FIELD TEST_FIELD COMPUTED AS
DATE(HireDate,”MM/DD/YYYY”)

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

TEST_Field Computed
DATE(HireDate,”MM/DD/YYYY”)

Both the above statements return the result where the new column ‘Test_Field’ has the first names all in small case as shown in the screenshot below:

As you can see in the result screenshot, there isn’t a whole lot of change in the way it is displayed. However, if you press “CTRL+i” you can see the data type for the new field ‘Test_Field’ is updated as a computed field. See screenshot below:

The use of this function allows analysts to get more information from the date field to perform additional analysis that my not be possible otherwise. Such transformations always lead to more possibilities. So keep exploring further. Check out the demo for the  Date function:

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.

 

DATE