Validate Datetime Datatype

Karthik_Anbusekar

i want to validate that the column datatype is "Datetime" Datatype.
i found only "Is Date" function, but i want to ensure that column is Datetime by adding rule in validate step of workflow.

Is there any function or work around to achieve this?

Thanks

Henry Simms

Once your data is in Data Studio, there's just one data type, DATE. Values in a lot of different formats can be parsed to DATE. The date and time display formats control how the values are shown in the UI, but the are stored in a consistent way.

Given that, it doesn't make sense to distinguish between DATE and DATETIME within Data Studio, but you may want to validate the format of values being loaded from, or exported to, and DBMS via an External System.

Can you clarify what you're looking to do?

Karthik_Anbusekar

Thanks Henry, So that solution we need to validate outside of experian l, ike in any DBMS
Is my understanding is right?

Henry Simms

No, you should certainly be able to validate what you need to within Data Studio. If you want to validate that values in Data Studio will successfully export to a database column that has eg DATETIME data type, the Is Date function will do that.

Karthik_Anbusekar

My requirement is , just want to validate the column that is Datetime datatype using validate step in workflow and show the results in experian dashboard

using Is Date function can i get results like below example

Column_1 Results
17-02-2025 08:15

18-02-2025 07:02

19-02-2025 15:39

20-02-2025 03:38

21-02-2025 23:33

Karthik_Anbusekar

My requirement is , just want to validate the column that is Datetime datatype using validate step in workflow and show the results in experian dashboard

using Is Date function, can i get results like below example ?

Column_1(Datetime)	Result
17-02-2025 08:15	True
18-02-2025 07:02	True
20-02-2025	False
21-02-2025 23:33	True
22-02-2025 05:07	True

Henry Simms

The value that is displayed without a timestamp, 20-02-2025, still has a time component (it is actually 20-02-2025 00:00:00), but you're not seeing it because of the display format I mentioned above:

"Date" in this context is just a Data Studio DATE type with a time component of 00:00:00.000

As such, all 5 of your example values are DATE and stored in the same way, and could be exported to a DBMS target column that requires date and time.

If you need to, you could create a function that checks if a time component is not 00:00:000. I used the built in GenAI function creator to do this, and it came up with a good solution:

param Input value;

var hour = GetHours([p:Input value]);

-- Extracts the hour component

var isValid = IsDate([p:Input value], true);

-- Checks if DATE is a valid date

var minute = GetMinutes([p:Input value]);

-- Extracts the minute component

var second = GetSeconds([p:Input value]);

-- Extracts the second component

And(

    [v:isValid],

    Not(

        And(

            Equals([v:hour], 0),

            Equals([v:minute], 0),

            Equals([v:second], 0)

        )

    )

)

Summary of the function:

• Use Get Hours, Get Minutes and Get Seconds functions to extract those values from any valid DATE
• Check that these do not equal zero, in addition to the Is Date check

Result (including test values also created by the GenAI:

An alternative, possibly simpler approach, would be to use the Format Date function to just extract the time component (HH:mm:ss.SSS) and then do a string match check on 00:00:00.000:

There may be better ways to achieve what you need too, depending on the source of your data and how it is being parsed when arriving into Data Studio