EXTRACTION OF DATES IN DD/MM/YYYY FORMAT FROM EXCEL
Hello. I have a project which requires me to extract a series of dates from a field named TERM_DATE from an uploaded Excel spreadsheet into Idea. The instruction I was given was to use TERM_DATE <> " in the equation editor, which is supposed to extract terminations not equal to blank but returns a Mismatch field type error. The dates are in the dd/mm/yyyy format as they appear in the database, but Idea is set to yyyy/mm/dd format. The extracion fails, I suspect it has to deal with the differnet date formats, and I've tried several times to append the date field to no sccess. I've done extractions before, but none involving dates, or had to deal with the issue of date formats. This is for a school assignment, I wouldn't be asking for help, but I've contacted the prof., and have receicved no guidance on the issue. I've included a copy of the Excel spreadsheet.
Hi Chocobo88,
Hi Chocobo88,
When you did the excel import did it bring it in as a date field? If you look in the field manipulation does it show up as a date field?
If this is a date field then the blanks should show as 0 so your equation would be the following:
TERM_DATE == "00000000"
In a date field what you see on your screen is based on your computer's regional settings but in the database IDEA stores the information as YYYYMMDD so if you are doing a search for a particular date or using dates in your analysis then you always want the date to be in the YYYYMMDD format.