[firebird-support] Casting as timestamp for dates prior to 30 December 1899

[Steve White [e-mail] [firebird-support]]

Hi anyone


Using Firebird 2.5.1, dialect 1 I find these curiosities:


select cast('29-DEC-1899 23:59:59' as timestamp) from rdb$database returns '30/12/1899 12:00:01 AM'


select cast('29-DEC-1899 00:00:01' as timestamp) from rdb$database returns '30/12/1899 11:59:59 PM'


Any date prior to 30 December 1899 behaves in a similar way:


select cast('15-JUN-1832 9:14:21' as timestamp) from rdb$database returns '16/06/1832 2:45:39 PM'


Looks like two whole days are added and then the time component is being subtracted. Works correctly after 30 December 1899. Is this a known issue or am I doing something I shouldn't?


Thanks,


Steve White

['Mark Rotteveel' [e-mail] [firebird-support]]

Where do you live (or what is your current timezone)? It might be explained by timezone changes, etc. That happened a lot before at the end of the 19th and stay of the 20th century.
Mark

----- Bericht beantwoorden -----
Van: "Steve White ***@health.telstra.com [firebird-support]" <firebird-***@yahoogroups.com>
Aan: <firebird-***@yahoogroups.com>
Onderwerp: [firebird-support] Casting as timestamp for dates prior to 30 December 1899
Datum: vr, nov. 4, 2016 07:20

Hi anyone

Using Firebird 2.5.1, dialect 1 I find these curiosities:

select cast('29-DEC-1899 23:59:59' as timestamp) from rdb$database
returns ‘30/12/1899 12:00:01 AM’

select cast('29-DEC-1899 00:00:01' as timestamp) from rdb$database
returns ‘30/12/1899 11:59:59 PM’

Any date prior to 30 December 1899 behaves in a similar way:

select cast('15-JUN-1832 9:14:21' as timestamp) from rdb$database
returns ‘16/06/1832 2:45:39 PM’

Looks like two whole days are added and then the time component is being subtracted. Works correctly after 30 December 1899. Is this a known issue or am I doing something I shouldn’t?

Thanks,

Steve White

[Helen Borrie [e-mail] [firebird-support]]

select cast('29-DEC-1899 23:59:59' as timestamp) from rdb$database returns ‘30/12/1899 12:00:01 AM’
select cast('29-DEC-1899 00:00:01' as timestamp) from rdb$database returns ‘30/12/1899 11:59:59 PM’
select cast('15-JUN-1832 9:14:21' as timestamp) from rdb$database returns ‘16/06/1832 2:45:39 PM’
Looks like two whole days are added and then the time component is
being subtracted. Works correctly after 30 December 1899. Is this a
known issue or am I doing something I shouldn’t?

I'm guessing you are doing these tests with a Delphi application. I
can't remember the exact reason why Delphi's rendering of timestamp
as DateTime does this but the curiosity has always been there. Someone
else might remember. Using a Delphi interface, w. Fb 2.5.6 w. Dialect
3, I still get curious results which are slightly different to yours
(although the 1832 example produces the same result as yours).

In isql:

Database: emp, User: sysdba
SQL> select cast('29-DEC-1899 23:59:59' as timestamp) from rdb$database;

CAST
=========================
1899-12-29 23:59:59.0000

SQL> select cast('29-DEC-1899 00:00:01' as timestamp) from rdb$database;

CAST
=========================
1899-12-29 00:00:01.0000

SQL> select cast('15-JUN-1832 9:14:21' as timestamp) from rdb$database;

CAST
=========================
1832-06-15 09:14:21.0000

Helen