Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.1
  HXTT Cobol v5.0
  HXTT DBF v7.1
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v6.1
  HXTT Paradox v7.1
  HXTT PDF v2.0
  HXTT Text(CSV) v7.1
  HXTT Word v1.1
  HXTT XML v4.0
Offshore Outsourcing
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
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT DBF
Daylight savings
Andrew
2019-09-16 17:16:08.0
We have Foxpro 2.01 DBF application and field is type DATE. When open raw table it only shows date (e.g. "4/1/2004") When query using Hxtt DBF driver it seems to convert to timestamp and also seems to apply daylight savings time (e.g. {ts '2004-04-02 00:00:00'} and {ts '2004-04-05 01:00:00'} - and I note that daylight savings was April 4 in 2004).

The issue is that we are having boundry issues when using a BETWEEN in WHERE clause because of that added hour in the date field. I have tried using DATE() function to just get date but it does not seem to work.

We are using Hxtt driver with Lucee/ColdFusion. How to fix?
Re:Daylight savings
HXTT Support
2019-09-17 04:01:35.0
Tried
select timestamp('4/1/2004'),date('4/1/2004'), {ts '2004-04-02 00:00:00'}, {ts '2004-04-05 01:00:00'} ;

create table if not exists testdate (d1 date,d2 timestamp);
insert into testdate values('4/1/2004','4/1/2004');
select * from testdate;

It seems normal.

What's your code?
Re:Daylight savings
HXTT Support
2019-09-17 04:14:52.0
Default it will use Calendar.getInstance()
Re:Re:Daylight savings
HXTT Support
2019-09-17 04:21:23.0
Calendar.java

public static Calendar getInstance()
{
return createCalendar(TimeZone.getDefault(), Locale.getDefault(Locale.Category.FORMAT));
}
Re:Re:Re:Daylight savings
HXTT Support
2019-09-17 04:23:04.0
What's your sql? HXTT DBF should use it as a java.sql.Date object, not a java.sql.Timestamp object.
Re:Re:Re:Re:Daylight savings
HXTT Support
2019-09-17 05:21:14.0
BTW, you can use {d '2004-04-01'} for date comparation.
Re:Re:Re:Re:Re:Re:Daylight savings
Andrew Kretzer
2019-09-17 11:22:05.0
Here is example of our SQL:

SELECT DATE, SO_NO
FROM table
WHERE DATE BETWEEN {d '2004-04-01'} AND {d '2004-04-05'}

The above SQL does not return rows where DATE is from 4/5/2004. However, this will give me rows from 4/5/2004:

SELECT DATE, SO_NO
FROM table
WHERE DATE BETWEEN {d '2004-04-01'} AND {ts '2004-04-05 23:59:59'}
Re:Re:Re:Re:Re:Re:Re:Daylight savings
HXTT Support
2019-09-17 17:01:15.0
Tried
create table if not exists testdate (d1 date,d2 timestamp);
insert into testdate values('4/1/2004','4/1/2004');
insert into testdate values('4/5/2004','4/5/2004');
select * from testdate;

select * from testdate WHERE d1 BETWEEN {d '2004-04-01'} AND {d '2004-04-05'} ;
select * from testdate WHERE d2 BETWEEN {d '2004-04-01'} AND {d '2004-04-05'} ;

No issue.

Please use create table asample SELECT DATE, SO_NO FROM table WHERE DATE BETWEEN {d '2004-04-01'} AND {ts '2004-04-05 23:59:59'}
to get asample file, then email support @ hxtt.com that file.


Re:Re:Re:Re:Re:Re:Re:Re:Daylight savings
Andrew
2019-09-17 17:54:17.0
Emailed file to you. Same problem.
Re:Re:Re:Re:Re:Re:Re:Re:Daylight savings
Andrew
2019-09-17 18:06:28.0
So it appears to work when I use straight SQL and fails when I use parameterized query?
Re:Re:Re:Re:Re:Re:Re:Re:Daylight savings
HXTT Support
2019-09-18 03:03:29.0
You send me a testdata.dbf, not your
create table asample SELECT DATE, SO_NO FROM table WHERE DATE BETWEEN {d '2004-04-01'} AND {ts '2004-04-05 23:59:59'}

> So it appears to work when I use straight SQL and fails when I use parameterized query?
Then it's wrong for
select * from testdate WHERE d2 BETWEEN ? AND ? ;
?
If so, you can try your jdbcurl?timezone=local or timezone=GMT-8 .

Maybe your program created a wrong java.sql.Date object?

You can also try setString(yourParameterIndex,"2004-04-05");

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Sitemap