Date function on Euro style dates

[GarrettC]

It is common in Europe that the year be at the beginning of the date, 2008/03/12, followed by the month and day.

The data set I received is in this format but the filemaker date functions seem to act like this is some kind of alien language. In the test fields using...

WeekOfYear ( 2008/03/12 )

DayOfWeek ( 2008/03/12 )

Year ( 2008/03/12 )

Yield the same outcome in the field, "?"

I have played with different combinations of file and system settings, field formats and field types and sometimes it works but only if I reverse the date to 03/12/2008. Then the functions work correctly, rendering the result needed.

I find it hard to believe that Filemaker cannot tolerate this date format and I am really surprised this has been so hard to do.

Before I write an elaborate script, running in aa loop to change the dates to another format can someone tell me what is going on? I prefer not messing with data, not to mention that one of the data sets has 1.5 million records that I would have to convert.

Help!

[comment]

2008/03/12 is not a date in any format. It's an expression that evaluates to 55.7777777777777778 (2008 divided by 3, and the result divided by 12). Year ( 2008/03/12 ) returns 1, not "?" as you state. This is independent of any date format you use.

If your file is set to use the y/m/d format, you should get a reasonable result using:

Year ( "2008/03/12" )

which is essentially the same as the explicit:

Year ( GetAsdate ( "2008/03/12" ) )

However, calculations that rely on a specific date format will fail when the format is changed, so it's better to use real date type values instead.

[fabriceN]

Hmmm... unless Japan is now considered European, I know no European country with such a date format.

International date format used in continental Europe is DD/MM/YYYY.

Anyway, you should use the Date function Date ( Month ; Day ; Year ). This will work everywhere.

[Søren Dyhr]

Careful here, Michael knows better than us ... from his veiw:

http://www.fmforums.com/forum/showpost.php?post/278536/

--sd

Edited May 16, 2008 by Guest

[fabriceN]

Excuse me Søren, I don't get it. Are you saying that you use YY/MM/DD in Denmark ?

I haven't stayed long there, and didn't notice that, but this would be extremely interesting, in the sense that obviously, this format is the more logical (date sort matches alpha sort), but I really wonder how much efforts and money would cost a whole country to switch to that format.

A real experience would be something to look at closely. (actually, they tried during the French Revolution, but already then, long before computers and information systems, they couldn't handle it).

Edited May 16, 2008 by Guest

[comment]

I know no European country with such a date format.

Hungary and Sweden?

http://en.wikipedia.org/wiki/Date_and_time_notation_by_country#Hungary

http://en.wikipedia.org/wiki/Date_and_time_notation_by_country#Sweden

Anyway, if the data follows this format, I don't think it matters what Garrett calls it.

[fabriceN]

Thanks Michael,

can someone from those countries (Sweden and Hungary) confirm this is really how dates are written in real life ? (although wikipedia seems pretty clear)

Edited May 16, 2008 by Guest

[Søren Dyhr]

Are you saying that you use YY/MM/DD in Denmark ?

No certainly not!!! ... and the thread I referred to did Michael suggest that all EU member states, should close ranks with the ISO standard they have signed to. My point was that allthough bureaucrats often might persuade polititicans to something - is it not nessersarily sifting thru down to the mere mortals way abouts.

Lets take an example cheese, no one in denmark is allowed to produce it without heat treating the milk, becasue the dairy products used to be cornerstone in our exports, is this done of hygeninc reasons - but tell a french farmer to abide to this would be dangerous, how would he get the flavors with sterile milk??

Standardization could only be considered as hint in direction of some sort, but europe is a patchwork of ethnic groups that never has intermingled in the scale the new world have done.

--sd

Edited May 16, 2008 by Guest

[comment]

did Michael suggest that all EU member states, should close ranks with the ISO standard

I only said it's not wise to dismiss the possibility of it happening. I believe changing the date format is a lot easier than say changing the currency...

[GarrettC]

Its amazing the conversation stirred up dates, whenever I deal with dates in any PDF/Javascript forum or Flash/Actionscript, the conversation is animated and wide ranging.

Unfortunately the conversation did not help me very much. I apologize about the mistake in regards to thinking the date format was European. The researcher I am helping is Dutch so she would have known better but the guy who works with her at Harvard, who scraped the data set was under that impression and that is how he delivered it to me, 4 digit year, month, day -2008/03/19.

She only has version 7 of Filemaker I have 8 Advanced and I am moving to 9 soon but I must deliver to her at that operational level.

As I stated in the post, the date functions are not working. They are placing a question mark in the field instead of the correct answer.

Can someone help me with this issue?

Thanks.

[comment]

Sorry, but what exactly is the issue? There's not a lot of information here. Is the data in a Date field, or is it in a Text field? What is the date format that the file is using? In what format was the original data (before you have imported it)?

[GarrettC]

I just wanted to add/ask...

Comment wrote "2008/03/12 is not a date in any format"

So are you saying Filemaker cannot handle this?

Also, I do not understand,"However, calculations that rely on a specific date format will fail when the format is changed, so it's better to use real date type values instead."

Can you explain what you are saying here?

[comment]

2008/03/12 is not a date in any format ... So are you saying Filemaker cannot handle this?

Filemaker handles it just fine - only not the way you seem to expect.

Can you explain what you are saying here?

I would prefer you clarify your situation first.

[fabriceN]

As Comment said, we need more information to help you.

But first of all, are you sure the field object on your layout is big enough to display a date ?

[Anuviel]

I am originally from Europe and have lived there quite long before moving to US. I always used dd/mm/yyyy format. Only in US did I start to use mm/dd/yyyy. When talking dates it is important to note that 5/3/08 or 3/5/08 are totally two different things and when talking with someone from a different country or having someone like that use a program it is best to declare date format in settings or wherever to avoid confusion.. What I believe, I might be wrong, is that Europe used to put day first as when someone asks what date it is, most of the people already know what month they are in so it is natural to put day first.

Format of yyyy/mm/dd or yyyy/dd/mm does not make much sense do me.

Just my two cents....

[Lee Smith]

The military or European way of writing a, 22 May 2008 (22/05/2008), or the common way in the US, May 22, 2008 (05/22/2008) can be set at the system level. If someone creates a file using either of these formats, the only way to convert them to the other format, is to Save a NO-RECORD clone of the file.

FileMaker stores the date as a number (i.e. 733184 represents the date of 5/22/2008. The number represent the number of days since 1/1/0001.

The way you Format a date to display, has nothing to do with the way the date is stored.

HTH

Lee

[comment]

Europe used to put day first

Europe is a big place.

[Lee Smith]

almost as big as Texas. :giggle:

[Søren Dyhr]

Europe is a big place

Crowded is more the expression fitting here. Density wise is it, only rivaled by India and China, the entire EU is is about 495000000 souls, not even including some Norwegians and Swiss and Icelanders being outside the EU.

So whether size really matter.... :

--sd

[fabriceN]

and... a few hundreds of millions of Russians !

[Genx]

The military or European way of writing a, 22 May 2008 (22/05/2008)

Lol, more like the non US / Canada / Federated States of Micronesia / Palau / Philippines method. Though to be fair Canada uses dd/mm/yyyy and yyyy/mm/dd as well and Philippines only use it when writing in english.

On a partially related note, can we use the GetAsDate(Substitute(YourField;"/";"+")) method to convert these dates in a text field and then change the field to date?

To be honest though I think it would've made everyone's lives easier if FM had just decided to store dates in the yyyy/mm/dd format but ohhh well.

Edited May 17, 2008 by Guest

[LaRetta]

Hi Alex! Good to see you around!!

I think it would've made everyone's lives easier if FM had just decided to store dates in the yyyy/mm/dd format but ohhh well.

I'm unsure how that would make things easier since then 3/4 of the world wouldn't match it. FM stores a date in the purest, simplest form ... a number. What we do with it is up to us. I don't think it gets simpler than that!

LaRetta :smile2:

[comment]

Actually, I believe Filemaker stores the dates as entered. It's hard to tell for sure with the .fp7 format, but that's how it was in previous versions and I don't see why they would change it. But the important thing is that in practice you can treat a stored valid date AS IF it were stored as a number.

can we use the GetAsDate(Substitute(YourField;"/";"+")) method to convert these dates

I believe so.

[Genx]

I'm unsure how that would make things easier since then 3/4 of the world wouldn't match it

True, but should it match SQL, then your percentage would fall dramatically :

Anyway, that being said you're probably right.

[GarrettC]

I am not going to ask all the questions here at once because its too confusing. If I could ask one thing at a time and step through the problem that would be very helpful. I have gone through all of Filemaker's help about what they refer to as File Settings. I cannot find any File Settings in the menus, I do find File Options but changing the options here is not fixing the problem.

First off I was given a data set by a guy who for some reason formatted the dates in this manner...

2002/11/20 2:51:00 AM

So I wrote a script and converted the dates in the fields to this....

11/20/2002 2:51:00 AM

The field is a Timestamp field as I want to retain the date and time accuracy.

However, when I attempt to change the formatting of the time in the field with the formatting menu it changes the field to double question marks as if its not true date and time.

My OS system date & time format is set to....

11/20/2002 2:51:00 AM

During the file open process I have tried to use either the option of File or System settings but the results, although slightly different, led to the same problems.

I carried out an experiment, I created a new field and set it to Timestamp and went into the Options and set it to a Auto-Enter Modification Timestamp. I did this because I wanted to see what kid of date the file would create. I was shocked to see that it did this format...

2002/11/20 2:51:00 AM

Where is the file getting this direction? If I choose File Settings on open where is this stored and more importantly how do I make it stop?

I am trying to get this file to a researcher, its not a for profit project, she is nervous and so am I, time is running out for us.

Any guidance to get out of this hole would be much appreciated. Thanks.

BTW I am delivering this to her in version 7 but I have 8 Advanced, both version have the same problem. I am working in both Tiger and Leopard but the dev work is mostly in Leopard.

[GarrettC]

Hey guys, I wanted to thank everyone for the long discussion but it did not address the issue I am having. Or maybe it did and I do no understand it.

I reposted the question, framed differently, with some new info.

"Huge frustration with dates & times"

Right now I have to say that Filemaker is just wasting my time. This should not be this hard to fix. The major complaint I have always had with Filemaker is that there are too many different places I have to go to change one parameter. Its a terrible interface.

If this was my project I would have chucked it but I am not going to give up on the promise I made this this PhD candidate.

If you can shed any light thanks so much.

[Raybaudi]

I carried out an experiment, I created a new field and set it to Timestamp and went into the Options and set it to a Auto-Enter Modification Timestamp. I did this because I wanted to see what kid of date the file would create. I was shocked to see that it did this format...

2002/11/20 2:51:00 AM

Hi

make a new experiment:

1) clone that file

2) create a new record

What is now inside the AutoEnter Modification Time Stamp ?

[comment]

This is a duplicate post:

If you didn't get an answer in the original thread, you should post back there. The reason you did not get a satisfactory answer there, I think, is lack of pertinent information. I have asked some specific questions in this post, but even now we don't know the answers.