How do I fix this Date Issue

sunlox

Forums

I have a very annoying issue with IDEA. I'm importing tables through SQL server and the dates come as numeric, a 6 digit number that starts with 7.
I realize there are a gazillion functions to convert numeric to date, but none of those actually work. They run but I end up with "ERROR" in place of the date records, in the date fields. 
Any other solution mentioned here or by IDEA doesn't work. But the issue can be fixed in Access or SQL Server, but not within IDEA.
Does anyone have an idea about this issue? What the IDEA help says about this doesn't work (apparently the numeric dates are in a format (not data type), that IDEA cannot understand)
In access I just use the CDate function minus a magic number, and that fixes it.

Brian Element Wed, 10/29/2014 - 19:47

Hi sunlox and welcome to the site. 

Would it be possible to give some examples of what you are downloading and the proper date it should be.  With that maybe someone might have some suggestions on how to get it to translate into a date format.

sunlox Wed, 10/29/2014 - 20:34

I'll have to check at work tomorrow but as far as I remember, a date like 5/12/2004 would probably be 733183 (numeric or character) in IDEA. 

Brian Element Thu, 10/30/2014 - 05:49

Thanks sunlox for the example.  I think I have seen this before, I will just have to remember how I handled it, once my brain cells kick in I will get back to you.

Brian Element Thu, 10/30/2014 - 07:54

Hi Sunlox, it looks like this is the number of days from the start of the roman calendar (Jan 1, 0000) to the present date.  The option in IDEA is just from 1900 to the present date so the trick to use that function is figure out how many days there are between 0000 and 1900, subtract that from your number and then use the @DaystoD function.  I will see if I can track down that amount, if you have exact number with the dates they represent it should be fairly easy to figure  it out.

Steven Luciani Mon, 11/03/2014 - 16:26

Hi Sunlox. What is the underlying system from which you are completing the SQL ODBC import into IDEA? By this I mean Great Plains, ACCPAC, etc.

sunlox Mon, 11/03/2014 - 16:55

Sorry for the slow response. Had a super busy Q3 Audit week. Phew.
@Brian Element, I can give your method a shot and see what happens. When you mentioned subtracting the numbers I felt confident :). I believe "734003" is converted Cdate to "8/17/2010".
@ Steve Luciani, I'll have to wait until Wednesday to find out what the underlying system is. Right now the DBA just connects my dbases to IDEA. I kinda wish I could just use SQL server but I can't complain much.
 
 

sunlox Mon, 11/03/2014 - 17:19

And by the way, I'm not that great with IDEA Scripting but where would I enter that date formula? Because if I use the Equation Editor through the "Modify Field" under "Data" menu, it requires me to enter a valid date mask, and there's really nowhere to do that, in the equation.
 

Brian Element Mon, 11/03/2014 - 19:56

Hi Sunlox, you need to create a virtual field, it sounds like you are just trying to change a field from numeric to date.

So what you want to do is double click on the database to open the field manipulation dailog, then click on append to add a new field, give the new field a name, under type select virtual date (or just date if you don't want to change the field later on) and enter the parameters you have above.

Steven Luciani Tue, 11/04/2014 - 08:11

Brian, I'm interested in how you got 693596. Based on your hypothesis that this system is starting its date from Jan 1, 0000 I calculate an adjustment factor of 693975.

Calculation:

365*1900 = 693500

1900/4 = 475 (To account the leap years during those 1900 years)

693500+475 = 693975.

Can you please provide your calculation.

sunlox Tue, 11/04/2014 - 08:25

I'm happy to report that this worked after almost 3 weeks of trying to find a solution! Thanks Brian.
@Steven Luciani, maybe Brian Element can provide the calculation but I can tell you right now that I cheated. An SQL guy gave me that "magic number" as he likes to call it. I also am very curious as to how it's calculated.