TexttoDate and regional settings

[jodibee]

Hi All,

I put together a Text to date calc field, with Date as output. Regional settings on both local and host machines are set to Autralia, ie. dd/mm/yyyy. However, the date returned from the calculation is in mm/dd/yyyy format. Anyone got some thoughts on how to rectify this?

[-Queue-]

What does your calc look like? I would think you'd need something like Date( Month( field ), Day( field ), Year( field ) ).

[jodibee]

It is TexttoDate(DOBday &"/"& DOBmonth &"/"& DOByear), I have been trying to kill two birds with the one calculation as I need to concantenate three separate fields into a valid date field instead of a text field. We have maintained the three separate DOB fields as the entire date of birth may not be known.

[-Queue-]

The "/" in your calc may pose a problem then. So what happens if DOByear, for example, is unknown? Your TextToDate() will fail.

[jodibee]

If any of the fields are unknown then the calc does not run, I expected that and that's OK. However, I still need to know how to amend the regional date settings so that is displays valid dates dd/mm/yyyy instead of mm/dd/yyyy

[-Queue-]

Another thought. Where was your file created? If it was in a different region, then you may need to save a clone to get the regional settings correct.

[Lee Smith]

Maybe

TextToDate(Date(Day(DOBday), Month(DOBmonth), Year(DOByear))

HTH

Lee

[-Queue-]

I don't think that will work because the DOB fields aren't dates.

[jodibee]

I made an error when I generated the original file, had new computer forgot to change the regional settings to the way we Aussies do it, so originally it was all in mm/dd/yyyy, I read on another form that this could pose a problem with TexttoDate. I will give Lee's suggestion a go and see what happens.

[-Queue-]

Try using the Set System Formats (On) script step.

[jodibee]

Nope didn't work, put in TextToDate(Date(Day(DateBirth_Day), Month(DateBirth_Month), Year(DateBirth_Year)))and nothing, literally.

[Lee Smith]

Are you getting a "?" for an answer, or a blank field?

[BobWeaver]

Try saving the file as a clone and see if the clone works okay.

[jodibee]

I had a startup script, it now reads

Set Use System Formats [On]

Enter Browse Mode []

Go to Layout [blah]

Show All Records

The Calc field is TexttoDate(DOBday &"/"& DOBmonth &"/"& DOByear) and it still came out at mm/dd/yyyy.

[jodibee]

Its not possible for me to generate a clone, its hosted on a Filemakerserver which I don't have access to. I have a local copy from yesterday which I could play with but it doesn't solve the problem.

[Wendy T]

hi Jodibee

I'm in Sydney too! Sounds like we have a similar set up -- I make sure every database uses Set System Formats [On] at start up. When doing date calcs, use the same formula as US folk would. I'd recommend using FileMaker's own date function (note the US-formatted inputs) instead of your TextToDate example.

Date( DOBmonth, DOBday, DOByear)

Make sure your calc result is set to Date, then set the Date Format to display however you wish.

re the clone issue - our dbs are also hosted on FileMaker Server so I share your pain about not being able to make a clone. Our IT guy loads them up using a machine that has US regional settings (!!) and I haven't yet convinced him to change them to Aus. However, the methods I outlined above work for us & I haven't had any problems with date calcs.

Good luck!

cheers,

Wendy

[Wendy T]

hi Jodi

this is from FM's help on the Date function:

"The order of the parameters in the Date function is always Month, Day, Year, no matter what operating system or FileMaker Pro date formats you are using."

and the TextToDate function:

"Parameter - any text expression or text field containing text in the same format as the date on the system were the file was created."

In other words, the Date function is more robust -- it's not dependent on the settings from when the file was created.

BTW, are you part of the same "major teaching institution" as me by any chance? I'm in Fac of Med, on the main campus in Camperdown.

cheers,

Wendy

[jodibee]

Hi Wendy,

I had thoughts of just changing to formula to what you said, will put it in place shortly as I currently have users on the system. Have also requested server access from IT to generate the clone and reload so that this doesn't happen anymore. I'm also Fac of Med at "that" institution but I'm out at Westmead, it's great to make touch with a local fellow FMP frustratee, will send you an e-mail.

:)

[-Queue-]

LOL @ fellow FMP frustratee

I almost read that as 'trustee'...

So now we're back to my original suggestion. Hope it works for you. What's with IT not being 'forced' to change the regional settings? I would think that could lead to major problems in other programs too.

[Dan-A]

How about using

Date( DOBmonth , DOB day , DOByear )

--Dan

[Dan-A]

How about using...

Date ( DOBmonth, DOBday, DOByear )

If each DOB field give a number then Date should work.

I have had similar problems with dates & text fields

even when i use the Use SystemFormats(on).

In fact, can anyone tell me what happens if...

I create a file in a system that use mm/dd/yyyy. If I copy that file to

a french system that use dd/mm/yyyy, when i set "Use system format(on)"

does the data(date) get saved in the original format of mm/dd/yyyy when my

file was created or in the format of dd/mm/yyyy because it was "created/copied"

to the new computer that uses a different format??

thanks

--Dan

[Dan-A]

oops !!

posted before looking at page 2 of all posts

Sorry

But if anybody can help with my quetion

I promise not to re-post as a new thread

--Dan

[CobaltSky]

Dan-A said:

In fact, can anyone tell me what happens if...

I create a file in a system that use mm/dd/yyyy. If I copy that file to

a french system that use dd/mm/yyyy, when i set "Use system format(on)"

does the data(date) get saved in the original format of mm/dd/yyyy when my

file was created or in the format of dd/mm/yyyy because it was "created/copied"

to the new computer that uses a different format??

--Dan

Dan, the 'Use System Formats' command has no effect on how the data is stored.

FileMaker *always* stores dates as a number which represents the days elapsed since 1/1/0001 (inclusive). So 29 August 2003 will always be stored as 731456 no matter what regional settings are or are not in place.

What 'Use System Formats' affects is:

1. How dates are displayed in date fields that do not have a custom date format specified (and which are therefore presented in the default format saved with the file, unless use system formats is invoked in which case they are displayed in the formats of the current system settings instead), and

2. How dates entered by the user are interpreted when being converted into the numeric format for storage - same default operability as for 1 above.

So, in short, the storage format never changes, only the interface handling mechanisms.

[Dan-A]

Hi Ray,

When you say

"... are presented in the default format saved with the file, unless..."

what happens if my file is created on a system with defaults: mm/dd/yyyy but

then copied to a system with defaults: dd/mm/yyyy ?

What becomes the default that is saved with the file?

I seem to have dates that are presented a certain way (without formatting)

but when i use copy & paste to "dump" the date in a text field in displays

in a different format.... from mm/dd/yyyy to dd/mm/yyyy !!??

Thanks

--Dan

(FYI i need to copy & paste because that's how the data gets

passed to an external function that is outside my control)

[CobaltSky]

Hi Dan,

The default formats are saved with the file at the time it is created and never change. So regardless of what formats are in place on the system the file is running on, the presentation formats at the file level remain what they were on the machine the file was created on.

By using the 'Uesr System Formats' command - either in a script or by selecvting it manually from the Format menu, the file defaults can be temnporarily over-ridden, but they are not changed and will be reinstated when the file is closed and reopened (unless 'Use System Formats' is called again).

The only way to change the default presentation and data entry formats that are saved with the file is to save a clone of the file and import the data into the clone. Because FileMaker treats a clone as a new file, it takes on default formats in line with those in place at the system level on the machine it is created on at the time it is created. Unless you do this, your file will retain its mm/dd/yyyy defaults regardless of where you uses it.

[Dan-A]

thank you so much for the help.

NOW i understand

thanks again

--Dan