SQL Server: Inserting Empty String in DateTime Column Becomes 1/1/1900

[homestar9]

Imagine you want to save a struct like this to the database:

myFruit = {
	"name": "Banana",
	"color": "Yellow",
	"eatenDate": ""
};

qb.from( "fruit" ).insert( myFruit );

One would think that when you query the fruit you just inserted, the eatenDate value would be null. However, in MSSQL server if you insert an empty string (''), it will set the date to 1/1/1900.

Now, you could fix this by explicitly setting myFruit.eatenDate to { value="", null=true }. However, this extra step should be optional since most developers would expect QB to interpret an empty string as null when persisting the data to the database.

[elpete]

This is tricky since qb has no knowledge that eatenDate is actually a date. And it is possible that you do want to insert an empty string into a column. I agree that it is unlikely, but I also have current instances where it actually inserts empty strings into columns.

Quick solves this by allowing you to specify what value is actually null. That defaults to an empty string, but can be overridden to be some other value if you need to insert empty strings. https://quick.ortusbooks.com/guide/getting-started/defining-an-entity#null-values

Could qb do the same? Possibly. It could be a query default that it checks. In any case it would be a breaking change.

[homestar9]

Thanks for getting back to me, @elpete.

I also like the way Quick handles empty strings/nulls.

I ran into this issue with QB with my integration tests where I quickly need to insert data into a database to run through my test specs. Perhaps we could avoid creating a breaking change by adding a new configuration parameter in QB called treatEmptyStringsAsNull, which could default to false for backward compatibility.

If treatEmptyStringsAsNull is true, the query param parser could loop through all of the passed params and look for empty strings. If it finds one, it would change the resulting struct from { value=value, cfsqltype=type } to { value=value, cfsqltype=type, null=true }

Of course, developers could work around this limitation, and I have done so in the past by always passing the full struct value representation rather than just sending simple values. However, a change like this could make QB even simpler to work with and make a developer's code that much cleaner.