AnsweredAssumed Answered

Does FileMaker Use Negative Day Numbers?

Question asked by richardsrussell on Jan 29, 2018
Latest reply on Jan 29, 2018 by user19752

Like • Show 0 Likes0
Comment • 7

This is a question more out of curiosity than need to solve a problem, but I'm not sure where else to find the answer, so I turn to this group of expert uses.

I know that FileMaker Pro stores dates as pure numbers, beginning at some point in the past, which I'll call D1 for "Day 1". I'm also pretty sure that D1 is not January 1 of the year 1 CE. But I've heard several different dates mentioned as to what D1 really is, and it's apparently different from one context to another. (Your operating system or Excel, for instance, may use a different D1 than FileMaker does.) What does FileMaker use as its D1?

And, if we want to refer to dates earlier than that (say for a database of historical records or genealogies), does FileMaker compute them (and then presumably store them) as negative numbers?

philmodjunk Jan 29, 2018 8:42 AM

Correct Answer

Getasdate ( 1 ) returns 1/1/0001 So the integer form of a FileMaker date can be defined as the number of days since 12/31/0000

values less than 1 produce evaluation errors and note that "AD" or "BC" type designations are not possible. Therefore FileMaker's date data type does not support dates earlier than this arbitrary value. I'm not an expert on the history of calendars, but you'll have issues with these dates long before you get to "day 1" as the calendar we use today did not exist back then and underwent significant adjustments more than once if I recall what I read correctly.

See the reply in context

No one else had this question

Outcomes

coherentkris Jan 29, 2018 8:46 AM

dates range from January 1, 0001 to December 31, 4000.

Today jan 29th 2018 would be stored as a positive integer 736723 which is the number of days from jan 1 0001

Actions

philmodjunk Jan 29, 2018 8:42 AM

Getasdate ( 1 ) returns 1/1/0001 So the integer form of a FileMaker date can be defined as the number of days since 12/31/0000

Actions

beverly

Jan 29, 2018 9:57 AM

Worth the repeat here, if you've already seen it on other forums:

(Monday humor?)

http://www.lix.polytechnique.fr/~dale/humor/y0k.html

Beverly

Actions

fmpdude @ beverly on Jan 29, 2018 11:59 AM

LOL. Thanks Bev!

Actions

Malcolm Jan 29, 2018 1:33 PM

Do you have a backlog of Cuneiform tablets that need to be entered?

From the help files :

FileMaker Pro stores dates as the number of days since January 1, 0001.

However, at FBA meetings the engineers have told us that data entered into a date field is stored as text.

When you are doing any sort of research, you'll find that dates are part fact, part theory and part fiction. In those situations I record day, month and year as text fields. I provide two copies of each because a lot of documents will offer date ranges, time periods or alternate dates. For example: aug-sep; the Easter weekend; the 4th or the 5th of May. If the source of the date is not already recorded, that's a good thing to keep too, otherwise you'll lose the sense. I've always used the Month field to do extra work, like entering "summer" when the text says "it was 1965 and it was summer." In practice, that works well for print and display because the fields are just being concatenated and you'll end up with "Summer 1965"" and that is both more expressive and more concordant with the text than "June 1965 - Aug 1965".

Malcolm

2 people found this helpful

Actions

richardsrussell @ philmodjunk on Jan 29, 2018 7:26 PM

As far as being an expert on calendars is concerned, I figure they've all been driven mad, probably for reasons comparable to the ones explained by Computerphile Tom Scott relative to times and timezones.

1 person found this helpful

Actions

user19752 @ Malcolm on Jan 29, 2018 7:30 PM

Text need to be stored for "as entered" field formatting.

For indexing number may be stored. But I guess there are some other values also stored (indexed), since finding using wild card (*/*/1) or day of week (sun) look using index.

2 people found this helpful

Actions

7 Replies

coherentkris Jan 29, 2018 8:46 AM
Mark Correct
Correct Answer
dates range from January 1, 0001 to December 31, 4000.
Today jan 29th 2018 would be stored as a positive integer 736723 which is the number of days from jan 1 0001
Like • Show 0 Likes0
Actions
philmodjunk Jan 29, 2018 8:42 AM
Unmark Correct
Correct Answer
Getasdate ( 1 ) returns 1/1/0001 So the integer form of a FileMaker date can be defined as the number of days since 12/31/0000

values less than 1 produce evaluation errors and note that "AD" or "BC" type designations are not possible. Therefore FileMaker's date data type does not support dates earlier than this arbitrary value. I'm not an expert on the history of calendars, but you'll have issues with these dates long before you get to "day 1" as the calendar we use today did not exist back then and underwent significant adjustments more than once if I recall what I read correctly.
Like • Show 0 Likes0
Actions
beverly Jan 29, 2018 9:57 AM
Mark Correct
Correct Answer
Worth the repeat here, if you've already seen it on other forums:
(Monday humor?)
- http://www.lix.polytechnique.fr/~dale/humor/y0k.html
Beverly
Like • Show 3 Likes3
Actions
fmpdude @ beverly on Jan 29, 2018 11:59 AM
Mark Correct
Correct Answer
LOL. Thanks Bev!
Like • Show 0 Likes0
Actions
Malcolm Jan 29, 2018 1:33 PM
Mark Correct
Correct Answer
Do you have a backlog of Cuneiform tablets that need to be entered?

From the help files :
FileMaker Pro stores dates as the number of days since January 1, 0001.
However, at FBA meetings the engineers have told us that data entered into a date field is stored as text.

When you are doing any sort of research, you'll find that dates are part fact, part theory and part fiction. In those situations I record day, month and year as text fields. I provide two copies of each because a lot of documents will offer date ranges, time periods or alternate dates. For example: aug-sep; the Easter weekend; the 4th or the 5th of May. If the source of the date is not already recorded, that's a good thing to keep too, otherwise you'll lose the sense. I've always used the Month field to do extra work, like entering "summer" when the text says "it was 1965 and it was summer." In practice, that works well for print and display because the fields are just being concatenated and you'll end up with "Summer 1965"" and that is both more expressive and more concordant with the text than "June 1965 - Aug 1965".

Malcolm
2 people found this helpful
Like • Show 1 Like1
Actions
richardsrussell @ philmodjunk on Jan 29, 2018 7:26 PM
Mark Correct
Correct Answer
As far as being an expert on calendars is concerned, I figure they've all been driven mad, probably for reasons comparable to the ones explained by Computerphile Tom Scott relative to times and timezones.
1 person found this helpful
Like • Show 0 Likes0
Actions
user19752 @ Malcolm on Jan 29, 2018 7:30 PM
Mark Correct
Correct Answer
Text need to be stored for "as entered" field formatting.
For indexing number may be stored. But I guess there are some other values also stored (indexed), since finding using wild card (*/*/1) or day of week (sun) look using index.
2 people found this helpful
Like • Show 0 Likes0
Actions

Does FileMaker Use Negative Day Numbers?

Outcomes

Related Content

Recommended Content