The Business wants this date attribute validated. I don’t know why, it’s a key field and I assume managed by the DB. Nonetheless, I need to build a rule.
But I think it’s strange that the Data Type is DATE while the date values are YYYY-MM-DD. I would expect to see DATE format data in the MM-DD-YYY HH:MM:SS AM format.
I don’t know how to code it in the date functions since it doesn’t have the standard date format and it doesn’t work in any string functions.
Any ideas?
Best answer by sam.dahl
Hi Tim,
It sounds like this is already a proper DATE type, so there’s no need for time or string functions. The way it’s displayed (YYYY-MM-DD vs. MM-DD-YYYY HH:MM:SS AM) is just a formatting choice, not usually how it's actually stored. In most DBMS, DATE fields are strictly typed and inherently validate as proper Gregorian dates so there’s no “format” in storage, just a native date representation.
If the goal is validation using Evaluation rules, then the usual things to check would be:
a) Missing values - are there nulls where there shouldn’t be?
b) Expected ranges - do dates fall within a logical timeframe (e.g., no orders from 1800)?
c) Common default or disallowed values - things like 1900-01-01, 9999-12-31, or placeholders that might indicate bad data.
d) Relativity & dependencies - does this date make sense in relation to others? (e.g., a ship date shouldn’t be before an order date.)
e) Uniqueness - probably not relevant unless this is actually a key field (which seems unlikely-maybe it’s just important rather than a true key).
If these dates were loaded from another source and incorrectly interpreted during ingestion, there could be a mix of issues-not just valid-looking but incorrect dates, but also records that were dropped entirely if parsing failed. That would likely have been framed as a bigger problem, though, so it’s probably not the case here.
One other possible issue is truncation or timezone shifts. If the original source included a time component and it was stripped to just a DATE, you could have unintended offsets-some systems default to midnight UTC when stripping time, which might cause subtle shifts depending on how time zones were handled. It’s an edge case, but worth considering if unexpected discrepancies show up.
Otherwise, if the field is already a strict DATE type in the DB, this is more of a standard data integrity check rather than a formatting issue.
Does that help?
Hi Tim,
It sounds like this is already a proper DATE type, so there’s no need for time or string functions. The way it’s displayed (YYYY-MM-DD vs. MM-DD-YYYY HH:MM:SS AM) is just a formatting choice, not usually how it's actually stored. In most DBMS, DATE fields are strictly typed and inherently validate as proper Gregorian dates so there’s no “format” in storage, just a native date representation.
If the goal is validation using Evaluation rules, then the usual things to check would be:
a) Missing values - are there nulls where there shouldn’t be?
b) Expected ranges - do dates fall within a logical timeframe (e.g., no orders from 1800)?
c) Common default or disallowed values - things like 1900-01-01, 9999-12-31, or placeholders that might indicate bad data.
d) Relativity & dependencies - does this date make sense in relation to others? (e.g., a ship date shouldn’t be before an order date.)
e) Uniqueness - probably not relevant unless this is actually a key field (which seems unlikely-maybe it’s just important rather than a true key).
If these dates were loaded from another source and incorrectly interpreted during ingestion, there could be a mix of issues-not just valid-looking but incorrect dates, but also records that were dropped entirely if parsing failed. That would likely have been framed as a bigger problem, though, so it’s probably not the case here.
One other possible issue is truncation or timezone shifts. If the original source included a time component and it was stripped to just a DATE, you could have unintended offsets-some systems default to midnight UTC when stripping time, which might cause subtle shifts depending on how time zones were handled. It’s an edge case, but worth considering if unexpected discrepancies show up.
Otherwise, if the field is already a strict DATE type in the DB, this is more of a standard data integrity check rather than a formatting issue.
Does that help?
+4Hi Tim,
In addition to what Sam already explained, there is also a distinction between a date and a datetime (or timestamp) datatype. In this case apparently the date is displayed without the time details, but it can also be displayed as date with 00:00:00 as time details.
If the business is only requesting to make sure that the values are only date values, so Tim's examples a-e don't apply, and indeed the date datatype applies for this field in the database, you can simply tell the business that their requirement is already met.
Kind regards,
Albert
Thank you, your replies are in line with my experience.
+3Thank you
Thank Sam and Albert.
My experience aligns with your responses.
I have passed your comments onto my mgr, who has the task (if he chooses) to convince the business users.
Another analyst developed this code to validate a datetime datatype. It seems valid to me:
In the Variables section:
In the logic section:
Not (VARIABLE_1 is not null)
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
