[Bug]: Blank form date fields are inserted into MSSQL tables as empty strings instead of null values

[lanedsmu]

trafficstars

Is there an existing issue for this?

• [X] I have searched the existing issues

Current Behavior

When inserting or updating a date field in MSSQL, the database engine treats empty strings and nulls differently: an empty string in MSSQL defaults to 1/1/1900, while NULL is, of course, null.

When tying an insert or update query to a Lowcoder form, a blank date field is inserted as an empty string, rather than as a null value.

Handlebar conditional logic as below errors out:

{{!frmDateField.value?"null":"'"+frmDateField.value+"'"}}

even though the string literal that this update query generated was perfectly valid, something like this:

update MyDateTable set MyDateValue='2024-08-23'
where MyDatePrimaryKey=42
``

or, conversely, when there was no value in the field, 
```sql
update MyDateTable set MyDateValue=null
where MyDatePrimaryKey=42

With the null value, the query fails with a date conversion SQL error.

When tracing the SQL, it became apparent why: query parameterization. What Lowcoder is doing:

exec sp_executesql N'update dbo.MyDateTable
  set MyDateValue=@P0
  where MyDatePrimaryKey=@P1 select SCOPE_IDENTITY() AS GENERATED_KEYS',N'@P0 nvarchar(4000),@P1 int',N'null',167

Which is absolutely the right thing to do. However, of course, 'null' != null. And hence the error.

Expected Behavior

Empty form field values are sent to the database engine as null values instead of empty strings.

Steps to reproduce

Create a simple form with a date field, tied to a create/insert database query on a MSSQL database.

Submit the form, with a blank value in the date field.

The new database entry will not be null, but instead will be 1900/01/01.

Environment

self-hosted docker multi on Linux

Additional Information

One workaround is to create a couple of additional queries to set those specific date values to null (not using a handlebar that would be parameterized except for the primary key). Then set the update query to run each of those new update queries at success, /if/ the date field on the form is blank.

[adnanqaops]

This has been Fixed. We didn't hear from any User about the occurrence of this issue again, so I am closing out this ticket. Kindly, feel free to open this ticket again if you face the issue again. Thank you.