All About Dates in IDEA

 

We have all faced that momentary feeling of unsurety while dealing with Date fields in databases imported to IDEA.

 

Whether it is applying Date Field Statistics or performing a Gap Detection on Dates or building a criteria with Dates, it is quite common to run into messages like 'Mismatch Field Type' or 'Field chosen is not of a Date Type'.

 

This primarily happens owing to the Date field getting imported into IDEA as a Character field which causes the prompt messages above.

 

So the most convenient way to work around this issue is to go to Field Manipulation and modify the Character field to Date. But upon making the edit to Date IDEA prompts you to enter a Mask to properly recognise the Date format in IDEA post edit.

 

So if you have a sample value like say 25-09-2018 in your Data as a Character field, you can change to Date and use the Mask DD-MM-YYYY

 

Another example like 25-SEP-18 should be Masked as DD-MMM-YY

 

Now once you make the conversion the Date will get displayed as per the regional date settings on your computer. So while you may have used a Sample Mask as DD-MM-YY to convert 25-09-18 from Character to Date. It will get displayed as per your regional date setting on your PC. So don't get perturbed by a different view of the Date. It is only a display setting.

 

Further let us recognise that Dates are stored in IDEA as YYYYMMDD specifically.

 

So while you are required to build any criteria around a static date such as Due Date of Proposal less than 25th September 2018, your criteria in the Direct Extraction will not look like Due Date < "25.09.18"

 

This would turn out to be a bad equation. It will be Due Date < "20180925" i.e. YYYYMMDD

 

So in conclusion we have a Mask to convert Character to Date, Display Date and Dates Stored in IDEA. Please do watch the three as they operate differently.

 

Best Regards

 

Group Admin Team