pcsoft.us.windevCopyright 2026, PC SOFT29 Mar 2019 08:07:59 Z23 Mar 2019 01:09:32 ZHi All, I have a simple query executed from a button after setting the date range in the form that produces unexplained results. [code:wl] ctInfoStore is Connection ctInfoStore..User = "****************" ctInfoStore..Password = "******************" ctInfoStore..Server = "localhost" ctInfoStore..Database = "InfoStore" ctInfoStore..Provider = hOledbSQLServer sGuestQuery is string = [ SELECT SURNAME, FIRSTNAME, EMAIL, TELEMOB FROM guest WHERE USERCODE IN ( SELECT DISTINCT GuestCode FROM BookingDetails WHERE Arrival > '%1' AND Departure > '%2' ) AND EMAIL LIKE '%@%' ] dsDataSet is Data Source IF HOpenConnection(ctInfoStore) THEN sQuery is string = StringBuild(sGuestQuery,DateToString(WIN_Main.EDT_ArrivalDate..Value, "YYYY-MM-DD"),DateToString(WIN_Main.EDT_DepartureDate, "YYYY-MM-DD")) Info(sQuery) IF HExecuteSQLQuery(dsDataSet, hQueryDefault, sQuery) THEN WIN_Main.STC_DB_Count..Text = HNbRec(dsDataSet) END ELSE Info(ErrorOccurred() +" - "+ HErrorInfo()) END [/code] The [code:wl]Info(sQuery)[/code] line produces [code:text] SELECT SURNAME, FIRSTNAME, EMAIL, TELEMOB FROM guest WHERE USERCODE IN ( SELECT DISTINCT GuestCode FROM BookingDetails WHERE Arrival > '2019-03-22' AND Departure > '2019-03-22' ) AND EMAIL LIKE '%@%' [/code] just as I expect, however, I was expecting a count of about 22, but I got a count of over 1300. On investigating what query the SQL Server was executing, I found this (Manually formated for readability) [code:text] SET FMTONLY ON SELECT COUNT(*) AS WDACCESNATIF_NBENR FROM ( SELECT "guest"."SURNAME", "guest"."FIRSTNAME", "guest"."EMAIL", "guest"."TELEMOB" FROM "guest" WHERE (( USERCODE IN ( SELECT DISTINCT "BookingDetails"."GuestCode" AS "GuestCode" FROM "BookingDetails" WHERE ( ( "BookingDetails"."Arrival" > '01-01-1992 01:01:01:000' ) AND ( "BookingDetails"."Departure" > '01-01-1992 01:01:01:000' ) ) ) ) AND "guest"."EMAIL" LIKE '%@%' )) WD_SUBQUERY SET FMTONLY OFF [/code] Obviously the reason for the high count result is because the dates are wrong. I asked for 2019-03-22, but I got 01-01-1992. Why is this happening, and how do I fix it? -- Colin30WEBDEVen_UShttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets/read.awpmoderateur@pcsoft.fr (El moderador)webmaster@pcsoft.fr (El webmaster)Colinpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-67001/read.awp29 Mar 2019 08:07:59 ZHmm.. Thought I had replied to both Garry and King's posts earlier.... OK. Found the solution. [code:text] IF HExecuteSQ…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-67001/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-67001/read.awpHExecuteSQLQuery changing by the time SQL Server gets it.kingdr8pcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66998/read.awp26 Mar 2019 19:53:31 ZHi Try this WHERE CONVERT(VARCHAR,Arrivale, 23) > '%1' AND CONVERT(VARCHAR,Departuree, 23) > '%2' where 23 is sqlDat…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66998/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66998/read.awpHExecuteSQLQuery changing by the time SQL Server gets it.kingdr8pcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66997/read.awp26 Mar 2019 19:48:30 ZHi Can you try this ... WHERE CONVERT(VARCHAR, Arrival, 23) > '%1' AND CONVERT(VARCHAR, Departure, 23) > '%2' wh…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66997/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66997/read.awpHExecuteSQLQuery changing by the time SQL Server gets it.gallen.dbmcpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66996/read.awp26 Mar 2019 14:50:30 ZHere are some other links that might help: https://forum.pcsoft.fr/fr-FR/pcsoft.us.windev/17045-mssql-date-and-time/read.awp h…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66996/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66996/read.awpHExecuteSQLQuery changing by the time SQL Server gets it.gallen.dbmcpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66995/read.awp26 Mar 2019 14:42:34 Zre: (1) Yes, I realize that WinDev produces the correct query (from WinDev's point of view) but it is obviously being misinterpr…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66995/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66995/read.awpHExecuteSQLQuery changing by the time SQL Server gets it.Colinpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66994/read.awp26 Mar 2019 07:09:47 ZThanks Garry, Had a quick look at the link in (1), but this won't help, as the query being generated by WinDev is incorrect, …https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66994/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66994/read.awpHExecuteSQLQuery changing by the time SQL Server gets it.gallen.dbmcpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66992/read.awp25 Mar 2019 15:07:09 ZTwo suggestions: 1: Try reading https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/ 2: Ask…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66992/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66992/read.awpHExecuteSQLQuery changing by the time SQL Server gets it.Colinpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66991/read.awp24 Mar 2019 00:40:15 ZHi Garry, Thanks for the thought. I tried that after posting, but no change :-( -- Colinhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66991/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66991/read.awpHExecuteSQLQuery changing by the time SQL Server gets it.gallen.dbmcpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66990/read.awp23 Mar 2019 17:44:47 ZThis is just a guess (I'm no SQL guru) but have you tried changing the line: IF HExecuteSQLQuery(dsDataSet, hQueryDefault, sQue…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66990/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/66989-hexecutesqlquery-changing-time-sql-server-gets-66990/read.awpHExecuteSQLQuery changing by the time SQL Server gets it.