Well, I would apreciate if you can help me with a 'date' function
issue (I have a IDS 7.31 on NT 4.0) When i run a sql like:

select
regtime,
date(regtime-1),
date(regtime),
date(365)
from grpieceregs

I returns:
regtime: 37993,6314815
date(regtime-1): 07/01/2004 <--the correct date
date(regtime): 08/01/2004 <--one day more!
date(365): 31/12/1900

What's wrong?

Thank's very much for your help!

Leandro
Patagonia Argentina

Whats the problem, date(365) is 365 days after the beginning of time
ie the last day of 1900, I'm not sure where the 6314815 is coming
from though, date(6314815) is way of the end of the dial by about
15K years

felipe wrote:
>
> Well, I would apreciate if you can help me with a 'date' function
> issue (I have a IDS 7.31 on NT 4.0) When i run a sql like:
>
> select
> regtime,
> date(regtime-1),
> date(regtime),
> date(365)
> from grpieceregs
>
> I returns:
> regtime: 37993,6314815
> date(regtime-1): 07/01/2004 <--the correct date
> date(regtime): 08/01/2004 <--one day more!
> date(365): 31/12/1900
>
> What's wrong?
>
> Thank's very much for your help!
>
> Leandro
> Patagonia Argentina

--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #

felipe wrote:
> Well, I would apreciate if you can help me with a 'date' function
> issue (I have a IDS 7.31 on NT 4.0) When i run a sql like:
>
> select
> regtime,
> date(regtime-1),
> date(regtime),
> date(365)
> from grpieceregs
>
> I returns:
> regtime: 37993,6314815
> date(regtime-1): 07/01/2004 <--the correct date
> date(regtime): 08/01/2004 <--one day more!
> date(365): 31/12/1900
>
> What's wrong?

I assume the value that you show for 'regtime' represents a date and time
value and is actually stored as a decimal. (Did I guess correctly?) Using
the first portion of that, I just ran a very simple test where I added 37993
(UNITS day) to 31 December 1899 and got back the 8 January 2004 date.
Simplifying the regtime value strictly for date (again, I'm making a guess
about your data and how it is stored), a regtime value of 1 would give you 1
January 1900 and a regtime value of 37993 gives you 8 January 2004. You
also see it where 365 returns 31 December 1900. These values represent the
number of days since 31 December 1899, so I'm pretty comfortable with the
date that you are getting back.

Just out of curiosity, how is the 37993,6314815 value generated and why is
it being stored in this manner rather than as a 'datetime' data type? Or am
I completely missing something about your setup and data?

--
June Hunt

"June C. Hunt" <june_c_hunt@hotmail.com> wrote in message news:<bthqov$7321r$1@ID-209514.news.uni-berlin.de>...
> felipe wrote:
> > Well, I would apreciate if you can help me with a 'date' function
> > issue (I have a IDS 7.31 on NT 4.0) When i run a sql like:
> >
> > select
> > regtime,
> > date(regtime-1),
> > date(regtime),
> > date(365)
> > from grpieceregs
> >
> > I returns:
> > regtime: 37993,6314815
> > date(regtime-1): 07/01/2004 <--the correct date
> > date(regtime): 08/01/2004 <--one day more!
> > date(365): 31/12/1900
> >
> > What's wrong?
>
> I assume the value that you show for 'regtime' represents a date and time
> value and is actually stored as a decimal. (Did I guess correctly?) Using
> the first portion of that, I just ran a very simple test where I added 37993
> (UNITS day) to 31 December 1899 and got back the 8 January 2004 date.
> Simplifying the regtime value strictly for date (again, I'm making a guess
> about your data and how it is stored), a regtime value of 1 would give you 1
> January 1900 and a regtime value of 37993 gives you 8 January 2004. You
> also see it where 365 returns 31 December 1900. These values represent the
> number of days since 31 December 1899, so I'm pretty comfortable with the
> date that you are getting back.
>
> Just out of curiosity, how is the 37993,6314815 value generated and why is
> it being stored in this manner rather than as a 'datetime' data type? Or am
> I completely missing something about your setup and data?


You got the idea: the decimals are the time values like:
0,6314815 = 15:09:20

I post the: date(365): 31/12/1900 just to show that it seems to work
OK.

Now the problem is:
With MS Excel 37993 is converted to 07/01/2004, and that happens also
with the VB functions, and also with Crystal Reports functions. (I use
that just to check)

But with the IDS funcion 'date' 37993 is converted to 08/01/2004

The database is part o a Marel system (www.marel.is) and i don't have
access to change the data or the way that it is stored :-(

I'm working with PHP and I couldn't find a function that helps me with
that.

felipe wrote:

> "June C. Hunt" <june_c_hunt@hotmail.com> wrote in message news:<bthqov$7321r$1@ID-209514.news.uni-berlin.de>...
>
>>felipe wrote:
>>
>>>Well, I would apreciate if you can help me with a 'date' function
>>>issue (I have a IDS 7.31 on NT 4.0) When i run a sql like:
>>>
>>>select
>>>regtime,
>>>date(regtime-1),
>>>date(regtime),
>>>date(365)
>>>from grpieceregs
>>>
>>>I returns:
>>>regtime: 37993,6314815
>>>date(regtime-1): 07/01/2004 <--the correct date
>>>date(regtime): 08/01/2004 <--one day more!
>>>date(365): 31/12/1900
>>>
>>>What's wrong?
>>
>>I assume the value that you show for 'regtime' represents a date and time
>>value and is actually stored as a decimal. (Did I guess correctly?) Using
>>the first portion of that, I just ran a very simple test where I added 37993
>>(UNITS day) to 31 December 1899 and got back the 8 January 2004 date.
>>Simplifying the regtime value strictly for date (again, I'm making a guess
>>about your data and how it is stored), a regtime value of 1 would give you 1
>>January 1900 and a regtime value of 37993 gives you 8 January 2004. You
>>also see it where 365 returns 31 December 1900. These values represent the
>>number of days since 31 December 1899, so I'm pretty comfortable with the
>>date that you are getting back.
>>
>>Just out of curiosity, how is the 37993,6314815 value generated and why is
>>it being stored in this manner rather than as a 'datetime' data type? Or am
>>I completely missing something about your setup and data?
>
>
>
> You got the idea: the decimals are the time values like:
> 0,6314815 = 15:09:20
>
> I post the: date(365): 31/12/1900 just to show that it seems to work
> OK.
>
> Now the problem is:
> With MS Excel 37993 is converted to 07/01/2004, and that happens also
> with the VB functions, and also with Crystal Reports functions. (I use
> that just to check)

[I originally wrote:]
That's wrong - 1900-02-28 was followed by 1900-03-01, but MS Excel and
compatible friends (Crystal, for example) believe there was a
1900-02-29. (1900 was not a leap year; it is divisible by 100 but not
divisible by 400).

[Fortunately, I checked before posting]
Excel for MacOS X formats 37993 as 2008-01-08 (note the year!) when
given that as a format. The base date for Excel is 1904-01-01, not
1900-01-01. What is even more weird is that -1 formats as
-1904-01-02, not as 1903-12-31; the negation seems to occur after
converting the absolute value to a date. Further, it appears to be
impossible to represent dates before 1904-01-01 in Excel (on MacOS X).
I don't know if this is a difference between Mac and Win Excel,
based on Mac heritage, or not...

OK - to the best of my understanding, there was some piece of DOS-ish
software (I can no longer remember which) that subsequently morphed
into a Windows product which only used a 'year divisible by 4' check
to establish whether a year is a leap year, hence allocating one too
many days to February 1900. Said piece of software corrupted things,
and I'd lay odds that's the root of your trouble.

[Even more fortunately, I bothered to boot my laptop running Win2K]
The results on Windows are different from the results on MacOS X. The
base date there is indeed 1900-01-01, but day 60 is 1900-02-29.
Negative numbers don't format at all on Windows - forget doing date
arithmetic on your genealogy data in Excel! My original thought
process was correct - on Windows.

Beware migrating spreadsheets between MacOS X (or earlier, but you
should upgrade anyway) and Windows.

> But with the IDS funcion 'date' 37993 is converted to 08/01/2004

Of course - we got that piece of arithmetic right.

> The database is part o a Marel system (www.marel.is) and i don't have
> access to change the data or the way that it is stored :-(
>
> I'm working with PHP and I couldn't find a function that helps me with
> that.

I don't know what to suggest. All the obvious answers are probably
not acceptable - fix the database design, fix the app, fix the o/s.
You could, if it was IDS 9.x, create a UDT Excel_Date() which
compensates for that particular piece of brain-death, but it still
involves fixing the database design. Or you have to remember to
coerce every date value (some variant on DECIMAL?) into an Excel_Date
and then into an IDS DATE type?

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

felipe wrote:

> "June C. Hunt" <june_c_hunt@hotmail.com> wrote in message news:<bthqov$7321r$1@ID-209514.news.uni-berlin.de>...
>
>>felipe wrote:
>>
>>>Well, I would apreciate if you can help me with a 'date' function
>>>issue (I have a IDS 7.31 on NT 4.0) When i run a sql like:
>>>
>>>select
>>>regtime,
>>>date(regtime-1),
>>>date(regtime),
>>>date(365)
>>>from grpieceregs
>>>
>>>I returns:
>>>regtime: 37993,6314815
>>>date(regtime-1): 07/01/2004 <--the correct date
>>>date(regtime): 08/01/2004 <--one day more!
>>>date(365): 31/12/1900
>>>
>>>What's wrong?
>>
>>I assume the value that you show for 'regtime' represents a date and time
>>value and is actually stored as a decimal. (Did I guess correctly?) Using
>>the first portion of that, I just ran a very simple test where I added 37993
>>(UNITS day) to 31 December 1899 and got back the 8 January 2004 date.
>>Simplifying the regtime value strictly for date (again, I'm making a guess
>>about your data and how it is stored), a regtime value of 1 would give you 1
>>January 1900 and a regtime value of 37993 gives you 8 January 2004. You
>>also see it where 365 returns 31 December 1900. These values represent the
>>number of days since 31 December 1899, so I'm pretty comfortable with the
>>date that you are getting back.
>>
>>Just out of curiosity, how is the 37993,6314815 value generated and why is
>>it being stored in this manner rather than as a 'datetime' data type? Or am
>>I completely missing something about your setup and data?
>
>
>
> You got the idea: the decimals are the time values like:
> 0,6314815 = 15:09:20
>
> I post the: date(365): 31/12/1900 just to show that it seems to work
> OK.
>
> Now the problem is:
> With MS Excel 37993 is converted to 07/01/2004, and that happens also
> with the VB functions, and also with Crystal Reports functions. (I use
> that just to check)
>
> But with the IDS funcion 'date' 37993 is converted to 08/01/2004
>
> The database is part o a Marel system (www.marel.is) and i don't have
> access to change the data or the way that it is stored :-(
>
> I'm working with PHP and I couldn't find a function that helps me with
> that.

It took a while

Select
date(51),
date(52),
date(53),
date(54),
date(55),
date(56),
date(57),
date(58),
date(59),
date(60)
from systables where tabid = 1;

produces

(constant) 20/02/1900
(constant) 21/02/1900
(constant) 22/02/1900
(constant) 23/02/1900
(constant) 24/02/1900
(constant) 25/02/1900
(constant) 26/02/1900
(constant) 27/02/1900
(constant) 28/02/1900
(constant) 01/03/1900

However, enter 51, 52, 53, 54, 55, 56, 57, 58, 59, 60 in an Excel
Spreadsheet column formatted as date and you get

20-Feb-1900
21-Feb-1900
22-Feb-1900
23-Feb-1900
24-Feb-1900
25-Feb-1900
26-Feb-1900
27-Feb-1900
28-Feb-1900
29-Feb-1900

So, do a google on "leap year 1900" and you get

181370 - Excel Incorrectly Assumes 1900 Is a Leap Year
Microsoft Excel incorrectly assumes that the year 1900 is a leap year. This
article ... Excel Incorrectly Assumes 1900 Is a Leap Year. View products ...
support.microsoft.com/support/ kb/articles/Q181/3/70.asp - 15k - Cached

aMUSEd