[solved] Date field importation and transformation

[iait]

We've noticed while importing tab delimited text files (which we do on a weekly basis) with dates formatted as 00/00/00 FileMaker transform it into 00/00/0000, but if it is a single digit month or day it will be 0/0/0000, this presents a problem for us, we need all single digit month and days to be stored as two digit month and days i.e. 02/08/2008

Any ideas?

-Silas

Edited August 4, 2008 by Guest

[LaRetta]

we need all single digit month and days to be stored as two digit month and days i.e. 02/08/2008

How dates are stored usually doesn't matter. FileMaker takes User dates in many formats, ie, with dashes, without year (will assume current) and so on. Then the Developer decides how to DISPLAY the dates as they wish.

So I believe you want to use the layout level formatting. From layout mode, select the field and Format > Date. You can specify leading Zero on days and months that way and also whether the year should display 2-digit or 4-digit.

[iait]

In fact we do DISPLAY the date in the format we need it, but the issue comes during cross database lookups and matching data based on these dates, which are inconsistent with our other dates in the 00/00/0000 format. 2/8/2008 doesn't = 02/08/2008?

[LaRetta]

2/8/2008 doesn't = 02/08/2008?

Hmm, well it certainly SHOULD match in relationships if both sides are true date fields. I am unsure what you mean by "during cross database lookups and matching data based on these dates" because, again, FileMaker sees them as dates regardless.

Can you explain a bit more of your situation? What are your Regional/Language Options set at? I would think imported dates would match, ie, does Mac have option for 'long date' instead of 'short date?'

UPDATE: To clarify, an OS setting usually gives options: M/d/yyyy or MM/dd/yyyy ... etc.

Edited August 4, 2008 by Guest
Added update

[Lee Smith]

Sounds like these are text fields to me.

Lee

[iait]

In fact the fields are Date fields, your suggestion above, about changing the computers date format to 00/00/0000 worked... the data after import is now stored as 02/09/2008.

Thanks Guys!

-Silas