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