Date Comparison during Validation

[Former Member]

Hello Experts,

I am doing some validations in MDM 5.5.

The requirement is to give error in case date is less than 01-Jan-2010. How this can be achieved?

Thanks

[Former Member · Accepted solution]

ABS(DATE FIELD)>=149384, this expression will return FALSE if the date is less than 1-1-2010.

BR,

Alok Sharma

[Former Member · Accepted solution]

Hi Alok,

Can you tell me how date is related to 149384?

Thanks,

Sagar

[former_member205403 · Accepted solution]

Hi,

I think this is no of days since 1600-01-01T00:00:00.000 and date with you want to compare (01-01-2010).

This seems to be good method and will allow you to create simple validation expression even when you want to check in between dates like 26-July-2009.

You can get this value by using [this link |http://www.convertit.com/Go/ConvertIt/Calculators/Date_and_Time/Date_Time_Diff_Calc.ASP]. Specify start date as 01/01/1601 and end date as 01-01-2010 (your date from which you want to compare)...Check the result, it will return as 149384 days.

Let waits for reply from Alok for some more details.

Regards,

Shiv

[Former Member · Accepted solution]

Thanks Siv. Your explanation was right and so I have given full points.

[Former Member · Accepted solution]

I would like to share here the logic for 149384.

Total Number of Year = Year given in default value - 1601.

i.e. 2010 - 1601 = 409.

Calculating no. Of leap years: 409/4 = 102.

So, Total Number of Days = (102 * 366) + (307 * 365) = 37332 + 112055 = 149387.

closer to 149384 because Sometimes you need to adjust the value by +/- 5 days from the actual calculated days.

For more details please refer:

/people/alok.sharma4/blog/2009/11/30/how-to-set-default-value-in-mdm-date-field

Hope this will give you more better idea.

Regards,

Mandeep Saini

[Former Member]

Hi Sagar,

I agree with Shiv, you can use any of above two expressions for validating your date.

I would like to add here, whatever format is there in MDM data Manager e.g. MM/DD/YYYY etc.

During validation expression date is read in this format only YYYY\MM\DD

You can also try for this expression too, which is more or less has same meaning as suggested by Shiv in other ways.

MID(XXXX,1,4) > 2009

Regards,

Mandeep Saini

[former_member205403]

Hi Sagar,

I could think of this Validation expression

IF( LEFT (XXXX, 4) >= 2010, IF ( MID (XXXX,6,2) >= 1, IF( MID (XXXX, 9, 2) >=1, TRUE, FALSE), FALSE ), FALSE )

Another expression can be

IF( LEFT (XXXX, 4) >= 2010, TRUE,  FALSE)

Here, XXXX is the date field. Please do not enter any constant or field name manually, rather use drop down.

It should throw validation error if the date is less than 1 Jan 2010.

But the logic can be more complex if you are checking with any date in between year, for example 26 June 2010.

Lets wait for experts if they can provide some other solution.

Regards,

Shiv