2. PHPRunner General questions
  3. sql variable - date type variable issue
This topic is locked

sql variable - date type variable issue

 		5/23/2020 09:31:51
PHPRunner General questions
J
 jacktonghk authorDevClub member

I am using MSSQL as the database. The default date format for it is 'YYYY-MM-DD'. $values follow SQL date format but sql variable like ':master.mydate' follows the windows' format which is 'DD/MM/YYYY' in my case. This makes my sql statement fails to run.
Generated :

SELECT [partner_code], [cusinv_key], [net_amt], [used_amt], [os_amt], [cusinv_date], cast(cusinv_key as varchar(20)) + ' $' + cast(os_amt as varchar(20)) AS [angljswk7i] FROM [dbo].[v_cusinv_os] WHERE ( ( ( partner_code = '1' and ) ) ORDER BY cusinv_key ASC
Should be :

SELECT [partner_code], [cusinv_key], [net_amt], [used_amt], [os_amt], [cusinv_date], cast(cusinv_key as varchar(20)) + ' $' + cast(os_amt as varchar(20)) AS [angljswk7i] FROM [dbo].[v_cusinv_os] WHERE ( ( ( partner_code = '1' and [color="#9ACD32"]cusinv_date <= '2020-05-14' ) ORDER BY cusinv_key ASC
I can change the regional setting on my pc to 'YYYY-MM-DD' in order to prevent the error but can I think SQL Variables should use the same date format as $values.
J
 		jacktonghk authorDevClub member 5/23/2020

I tried to set up the date format of my date fields to 'YYYY-MM-DD' in PHPrunner. However, it also changed my regional setting on the Misc page, therefore, changed my currency.
J
 		jacktonghk authorDevClub member 5/24/2020

I found this may be a bug if I use autofill in my lookup wizard.
In my lookup wizard's where clause, I compare a date field with the value from the master table as below.
partner_code = ':master.partner_code' and cusinv_date <= ':master.paid_date'
The SQL generated for the dropdown list is correct, it uses YYYY-MM-DD but the SQL generated for the autofill is incorrect, it uses DD/MM/YYYY as in my windows' regional setting. The date format should be YYYY-MM-DD which follows the SQL rule.
J
 		jacktonghk authorDevClub member 5/26/2020

Hi administrator,
I don't know how to solve this issue. You can see from the below error messages, it seems PHPR returns the :master.field values in 2 different date format for the lookup where condition and autofill. I have no clue to solve this issue, the only work around I can do is to change the regional settings in the Windows' control panel to YYYY-MM-DD format. Please help!!
Error type 256

Error description Source: Microsoft OLE DB Provider for SQL Server

Description: Conversion failed when converting date and/or time from character string.

URL localhost/ipms/autocomplete.php?

Error file C:\xampp\htdocs\ipms\connections\Connection.php

Error line 626

SQL query SELECT [partner_code], [cuspay_key], [paid_amt], [used_amt], [os_amt], [paid_date], cast(cuspay_key as varchar(20)) + ' ' + cast(os_amt as varchar(20)) AS [a28saqi8s6] FROM [dbo].[v_cuspay_os] WHERE ( ( ( partner_code = '1' and paid_date <= left('19/05/2020',4)+substring('19/05/2020',6,2)+right('19/05/2020',2) ) ) ) ORDER BY cuspay_key ASC
Error type 256

Error description Source: Microsoft OLE DB Provider for SQL Server

Description: Conversion failed when converting date and/or time from character string.

URL localhost/ipms/tx_cusinv_allocation_add.php?

Error file C:\xampp\htdocs\ipms\connections\Connection.php

Error line 626

SQL query SELECT [partner_code], [cuspay_key], [paid_amt], [used_amt], [os_amt], [paid_date], cast(cuspay_key as varchar(20)) + ' ' + cast(os_amt as varchar(20)) AS [aiwou02ul4] FROM [dbo].[v_cuspay_os] WHERE ( ( ( partner_code = '1' and paid_date <= right('2020-05-19',4)+substring('2020-05-19',4,2)+left('2020-05-19',2) ) ) ) ORDER BY cuspay_key ASC
J
 		jacktonghk authorDevClub member 5/26/2020

The problem always happen if the PHPR locale's date format is not 'YYYY-MM-DD". Does anyone have the same issue when using a date SQL variable in the lookup wizard where clause?