Skip to main content

How to Verify Date Format?

In this use case, we will guide you through the process of validating date values in a table using Adventure Works as the data source. Our goal is to ensure that date columns, specifically "SellStartDate" and "SellEndDate," adhere to a predefined date format. By following these step-by-step instructions, you can effectively validate your date data and identify any inconsistencies.

Steps​

Below are the steps outlined in the video:

Create Rule​

  1. Start by creating a new validation rule. Give the rule an appropriate name and select the source connection as "Adventure Works," the schema as "production," and the table as "product."

Preview Data​

  1. Before proceeding, take a moment to preview the data. Examine the available date columns, which include "SellStartDate," "SellEndDate," "DiscontinuedDate," and "ModifiedDate." These columns should follow a standard date format, and we will validate this format.

Add Date Checks​

  1. Go to the "Check" tab and add a check specifically for the date type. Select "SellStartDate." You can choose from pre-existing date formats such as "DD MM YY" or "MM DD YY," but in this case, we need a custom format. Specify the format as "YYYY MM DD HH MM SS.S" (note the case sensitivity), and save the check.

  2. Create another check for the "SellEndDate" column using the same custom format. Save the check.

Include Product ID​

  1. For easy identification of any validation mismatches, include the "ProductID" from the source. This addition allows you to pinpoint the specific product and column involved in case of discrepancies.

Publish & Execute​

  1. Review the added checks and publish the rule. Execute the rule to validate the date values in the table.

Review Result​

  1. After execution, review the results. The status should indicate success, confirming that all "SellStartDate" and "SellEndDate" values adhere to the expected format. This means that the dates in your table have been successfully validated.

Video: How to Verify Date Format?​

Conclusion​

By following this use case, you can ensure that date values in a table, such as "SellStartDate" and "SellEndDate," conform to the expected format. Identifying and addressing any date format inconsistencies enhances data accuracy and reliability in your projects. You can apply similar approaches to validate other columns or data sources as needed.

Last updated on