Page 1 of 2
Problem with SQL Native
Posted: Wed Feb 02, 2022 12:52 pm
by ORibeiro
Hello Friends,
With this command in oQry:Query() in MySQL
WHERE cases.date BETWEEN '2022-01-01 00:00:00' AND '2022-01-12 23:59:59';
Return 31 records, the last two with date '2022-01-13 00:47:50'
Using the same Select in HeidiSql, 29 records are returned correctly within the period 01 to 12 January.
Has anyone had this same problem with the native class?
Thanks.
Re: Problem with SQL Native
Posted: Wed Feb 02, 2022 3:50 pm
by nageswaragunupudi
Has anyone had this same problem with the native class?
No.
I will prepare a sample to test and post here soon.
One point, though not relating to your issue:
Do not use BETEEN while dealing with DateTime Fields.
For example, "AND 2022-01-12 23:59:59" excludes timestamp 2022-01-12 23:59:59.100, though this is still 12th Jan.
Better <field> >= <date1> and <field> < ( <date2> + 1 )
Re: Problem with SQL Native
Posted: Wed Feb 02, 2022 4:37 pm
by ORibeiro
Thanks Raw,
I modified to:
WHERE date(casos.data)>=date('2022-01-01') AND date(casos.data)<date('2022-01-12')+1;
The result is 31 records, like before.
There are two records in casos.data = date('2022-01-13') in the result.
I don´t know why.
Do you have another idea?
Re: Problem with SQL Native
Posted: Fri Feb 04, 2022 4:20 am
by nageswaragunupudi
Is casos.data a DateTime field?
Why do you use Date(casos.data) ?
Please let us know the exact field type of casos.data
Re: Problem with SQL Native
Posted: Fri Feb 04, 2022 2:24 pm
by ORibeiro
Hi Raw,
The type of field is TIMESTAMP.
If I don´t put date(casps.data) the query does´t work and return all records.
Re: Problem with SQL Native
Posted: Sat Feb 05, 2022 12:14 pm
by ORibeiro
Hello,
I would like to know if this issue in select with TIMESTAMP field has been fixed in newer versions of FWH.
The same SELECT in SQLRDD and HeidiSQL brings the correct records, but in native mode it brings records from other dates.
Thanks.
Re: Problem with SQL Native
Posted: Sat Feb 05, 2022 6:05 pm
by nageswaragunupudi
Code: Select all | Expand
"SELECT ...... WHERE casps.data >= '2022-01-01' AND casps.data < '2022-01-13'"
Re: Problem with SQL Native
Posted: Sun Feb 06, 2022 10:30 am
by ORibeiro
Unfortunately it didn't work.
Could the problem be with my DLL version?
Re: Problem with SQL Native
Posted: Sun Feb 06, 2022 11:26 am
by Jimmy
hi,
did you use
when "compare" TIMESTAMP
as i know ORDER BY use UTC but you want "Local Time" so try
Re: Problem with SQL Native
Posted: Mon Feb 07, 2022 2:01 pm
by nageswaragunupudi
We created a small table `test_timestamp` on our Fivewin Cloud server, which is accessible to all of us for demo purposes.
This is the full table data.
We want to extract rows with timestamp between 01-02-2022 and 04-02-2022 (both days inclusive). We used where clause like this:
Code: Select all | Expand
SELECT * FROM test_timestamp WHERE col_timestamp >= '2022-02-01' AND col_timestamp < '2022-02-05'
This is the result:
You can yourself run this program. Copy the program given below to \fwh\samples folder and build and run with buildh.bat or buildx.bat.
Code: Select all | Expand
#include "fivewin.ch"
function Main()
local oCn, oRs
local t
SET DATE ITALIAN
SET CENTURY ON
oCn := FW_DemoDB( 6 )
if oCn == nil
? "connect fail"
return nil
endif
oCn:lShowErrors := .t.
oRs := oCn:RowSet( "SELECT * FROM test_timestamp" )
XBROWSER oRs TITLE "FULL TABLE"
oRs:Close()
oRs := oCn:RowSet( "SELECT * FROM test_timestamp WHERE col_timestamp >= '2022-02-01' AND col_timestamp < '2022-02-05'" )
XBROWSER oRs TITLE "01 FEB TO 04 FEB"
oRs:Close()
oCn:Close()
return nil
You can even modify the program and test whatever you like.
This is the structure of the table:
Code: Select all | Expand
CREATE TABLE `test_timestamp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`COL_DATETIME` DATETIME(3) DEFAULT NULL,
`COL_TIMESTAMP` TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Re: Problem with SQL Native
Posted: Mon Feb 07, 2022 2:09 pm
by nageswaragunupudi
Jimmy wrote:hi,
did you use
when "compare" TIMESTAMP
as i know ORDER BY use UTC but you want "Local Time" so try
It is not necessary to ORDER BY THE field for setting WHERE clause on that field.
We need to consider UTC offset if and only if we are accessing the same database over internet from different timezones/countries.
Re: Problem with SQL Native
Posted: Mon Feb 07, 2022 2:09 pm
by nageswaragunupudi
Jimmy wrote:hi,
did you use
when "compare" TIMESTAMP
as i know ORDER BY use UTC but you want "Local Time" so try
It is not necessary to ORDER BY THE field for setting WHERE clause on that field.
We need to consider UTC offset if and only if we are accessing the same database over internet from different timezones/countries.
Re: Problem with SQL Native
Posted: Mon Feb 07, 2022 2:12 pm
by Rick Lipkin
Jimmy
I know you mentioned Sql Native .. MS Access has a bit different syntax .. you have to put # around your date variables
Code: Select all | Expand
"[InvoiceDate] >= #"+dtoc(lDate)+"# And [InvoiceDate] <= #"+dtoc(hDate)+"#"
Otherwise
Code: Select all | Expand
"[InvoiceDate] >= '"+DTOC(LDATE)+"' and [InvoiceDate] <= '"+DTOC(HDATE)+"'"
You have to use ' ' around your date variables.
Rick Lipkin
Re: Problem with SQL Native
Posted: Mon Feb 07, 2022 2:23 pm
by nageswaragunupudi
Rick Lipkin wrote:Jimmy
I know you mentioned Sql Native .. MS Access has a bit different syntax .. you have to put # around your date variables
Code: Select all | Expand
"[InvoiceDate] >= #"+dtoc(lDate)+"# And [InvoiceDate] <= #"+dtoc(hDate)+"#"
Otherwise
Code: Select all | Expand
"[InvoiceDate] >= '"+DTOC(LDATE)+"' and [InvoiceDate] <= '"+DTOC(HDATE)+"'"
You have to use ' ' around your date variables.
Rick Lipkin
DTOC() does not work for MySql.
Another thing, there a few things which work for Americans but not others in the world. because some database servers accept American notation of dates.
For example your syntax of DTOC() does not work for me in India even on MSACCESS.
In these forums, please post solutions which are Universal.
Re: Problem with SQL Native
Posted: Mon Feb 07, 2022 4:56 pm
by ORibeiro
Raw,
I tried to run your example but returned the error: 1045 Acess denied for user 'sql7148817@'189-46-180-40.dsl.telesp.net.br (using password: YES).
Thanks