Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Cobol v2.1
  HXTT DBF v5.2
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT ACCESS
Dates stored as Java dates, not OLE date
Ciaran Keating
2008-09-01 23:09:20.0
Hi,

We're trying to set a date column to zero ("update table set datecolumn=0"). HXTT JDBC always sets it to 1970-01-01. I presume that's because in Java the value zero represents that date.

However, this is an Access database and we need a reliable way to set a date value to zero so that non-Java clients can interpret it correctly in the Windows/OLE world.

But trying to work around it, we tried to subtract the difference between 1970-01-01 and 1899-12-30, which is 25569 days:

update table set datecolumn=date_sub(0,interval 25569 day);

but that yields a value of 1899-12-30 11:00:00 (we're in Sydney, GMT+10:00). I'm guessing that (a) your component is adding our timezone offset, and (b) it's assuming that we're in daylight savings time.

If it is adding the timezone offset (but why would it do that?) I can subtract it, no big deal. But we're not in daylight savings time and so I can't find a reliable way to subtract that extra hour.

We can't find any timezone functions in your documentation. Is there a way to set a date column reliably to zero (1899-12-30)?


Cheers,
Ciaran
Re:Dates stored as Java dates, not OLE date
HXTT Support
2008-09-02 01:06:19.0
>We're trying to set a date column to zero ("update table set datecolumn=0"). HXTT
>JDBC always sets it to 1970-01-01. I presume that's because in Java the value
> zero represents that date.
You should use update table set datecolumn=null;
Re:Re:Dates stored as Java dates, not OLE date
Ciaran Keating
2008-09-02 01:21:05.0
Hi,

Null is not the same as zero.


Cheers,
Ciaran
Re:Re:Re:Dates stored as Java dates, not OLE date
HXTT Support
2008-09-02 01:46:20.0
Try
update table set datecolumn='1899-12-29 23:00:00';
or
update table set datecolumn={ts '1899-12-29 23:00:00'};
Re:Dates stored as Java dates, not OLE date
Ciaran Keating
2008-09-02 17:06:09.0
We'd be happy to set it explicitly to 1899-12-30 00:00:00, but that still yields the wrong value (it gives us 1899-12-30 01:00:00). We're not happy to arbitrarily subtract an hour - how do we know that that will always be correct? For example, if it has something to do with daylight savings time (somewhere!) then what happens when that somewhere is not in daylight savings time?

In short, there's something wrong here. If you can explain what's wrong, we'll be happy to work around it. For example, maybe your driver assumes it's in GMT and applies GMT daylight savings. If that's the case, then we can work out whether GMT is currently in daylight savings and subtract the hour, or not, as appropriate. Such a workaround would be reliable, because it would be based on a correct understanding of the problem.

Re:Re:Re:Re:Re:Dates stored as Java dates, not OLE date
HXTT Support
2008-09-02 17:15:58.0
Because MS Access uses 1899-12-29 23:00:00 as 0:00:00 . It hasn't relation with daylight savings time or timezone.
Re:Dates stored as Java dates, not OLE date
Ciaran Keating
2008-09-02 17:38:59.0
I don't believe that's correct. As I understand it, Access uses the OLE date format, where zero means 1899-12-30 00:00:00.

Of course you're right that the internal storage of the date in Access has nothing to do with DST or timezones. However, I'm trying to guess why your JDBC driver converts 1899-12-30 00:00:00 to 1899-12-30 01:00:00. For some reason an hour is added to the date before it is stored in the database.
Re:Re:Re:Re:Re:Re:Re:Dates stored as Java dates, not OLE date
HXTT Support
2008-09-02 18:54:19.0
>However, I'm trying to guess why your JDBC driver converts 1899-12-30 00:00:00 to
>1899-12-30 01:00:00. For some reason an hour is added to the date before it is
>stored in the database.
Because your sql is using 0(an int value), not the standard {ts '...'} or {d '...'}. HXTT sql engine will changed that int value to a timestamp according to its rule. That convert hasn't relation with MS Access format too.
Re:Dates stored as Java dates, not OLE date
Ciaran Keating
2008-09-02 23:41:45.0
Yes, you're right about the integer value. My code 'date_sub(0,interval 25569 day)' was invalid because DATE_SUB expects a fully-specified date.


However, if I apply either of these valid statements:

update answer set lastchangedate={ts '1899-12-30 00:00:00'}
update answer set lastchangedate={d '1899-12-30 00:00:00'}

and then view the data in Access, Access says

1:00:00 am

If I read this value using raw ODBC in C++ then the column is shown to be of type SQL_TYPE_TIMESTAMP and the TIMESTAMP_STRUCT value is

year = 1899
month = 12
day = 30
hour = 1
minute = 0
second = 0
fraction = 0

Clearly there's an error somewhere.

Perhaps your JDBC driver maps zero to '1899-12-29 23:00:00'. If that's the case, then we're happy to set that value in our code.

Can you confirm that this is the case?
Re:Re:Re:Re:Re:Re:Re:Re:Dates stored as Java dates, not OLE date
HXTT Support
2008-09-03 22:40:58.0
You should use standard {t '00:00:00'}. A latest package, which has the compatible date fromat show, will be available after about 12 hours.
Re:Dates stored as Java dates, not OLE date
Ciaran Keating
2008-09-08 16:51:48.0
Thanks, this works perfectly well.

Cheers,
Ciaran

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap