Importing from ODBC text into date

[Mare]

Hi - I currently have 2 filemaker pro databases set up. The first one a person manually imports from an ODBC location. The second one a script imports from the first. Recently I decided I wanted to join them together with one script so I could hand the file off to someone and they could get push button results.

So the method I chose was to try to have the second file import directly from the ODBC location. When I did this the date that it imported seemed to change format resulting in junk.

In the first file the field is set as date and the value will read in automatically in the 20040323 format. In the second file the field is also set as date but it will come in as 90209. That appears to be more of a text format for the number.

Any ideas?

[Squeaky Data]

You may need to convert from text to date using:

GetAsDate ( text )

[Lee Smith]

You have to convert the data into a Date Fromat that FileMaker can recognize.

i.e.

Date ( Middle ( YourField ; 5 ; 2 ) ; Right ( YourField ; 2 ) ; Left ( YourField ; 4 ) )

HTH

Lee

[Mare]

I have actually tried both of those conversion functions. The first one GetAsDate ( text ) just gives me a '?'. The second one gives me a '?' on some of the #s and a junk date on other numbers.

Is it possible to have two filemaker pro applications open at the same time so I can be walking through each one's import 'setup' step by step in parallel?

I have already tried going back and forth under the windows tab and I can't see a difference in the import setup that could be resulting in this difference. In both of them my source field is Loc_start_time and my target field is Dateset and

my field type is Date.

Anything you can think of that I could have overlooked or accidentally tweaked? I'm totally perplexed by it.

[Lee Smith]

Like I said, the Data has to be recognized as a Date. The Calculation I provided will do it based on the "one" example you gave. However, the data has to be consistent.

The ? means that the data in that field doesn't match the number or format of the one you provide.

Post a bigger sample of the data, the import file, or give examples of the data that comes up with ? as an answer.

Lee

[Squeaky Data]

You may need to import your date data into a text field first and then applie Lee's calculation to translate the text into a date.