How to set empty date?

Databases
21

Have you researched to see if Firebird suffers from the same issue the mySQL does in regards to this?
Have you tried testing this using pure SQL instead of via a recordset?
Have you responded to Gregs request for more information?

22

Have you tried assigning an empty string?

23

this works for me in some of my apps, for sqlite or postgres. not tested on other databases.
sets a date column to null.

24

[quote=392198:@Jean-Yves Pochez]this works for me in some of my apps, for sqlite or postgres. not tested on other databases.
sets a date column to null.[/quote]
This does not work for all database engines (mySQL for example)

25

I fiddled around with different options for dates. I settled on storing dates as SQLDate strings in the database. When I recover the dates from the db and want to fill a date object, it is still quite simple.

Saving an empty date is as easy as saving “”.

26

this is also the route I have taken, except when working with Oracle, which has exceptional date handling ability

27

I’ll second what @Jean-Yves Pochez said, and try using SQLExecute. Additionally, you could try:

rs.Edit rs.Field("MyColumn").Value = "NULL" rs.Update

It’s not technically correct, but your database may not like the way Xojo passes Nil.

28

The docs do say

You might try .NativeValue (… untested)

29

@Jonathan Eisen assigning “NULL” does not work either
@Jeff Tullin error: cannot assign value to this property
@Dave S do you mean by using a character field to store dates as string?

30

In SQLite’s lang_createtable.html they said:

A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind. The default value of each column is NULL. .
If you do not feed the DATE with anything, it will stay (probably) as NULL.

What said the Firebird Language Reference ?

These books are (per these books) a translation from Russian into English, so you may not found what you are searching…

Read “Firebird_Language_Reference_25EN.pdf”, page 29, the paragraph just before “Conversion of Data Types”.

URL:
https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/Firebird_Language_Reference_25EN.pdf, https://firebirdsql.org/en/reference-manuals/

31

[quote]@Dave S do you mean by using a character field to store dates as string?
[/quote]

Yes.
if you only need the date component, this is a pretty reliable and cross-database method of doing dates
String field holding YYYYMMDD
So 20180415 is 15th April
Sorting this column will sort by date correctly.
ranges are easy
And you can convert to other forms of date for calculation purposes if you need to.

A null here can actually be a null, or you might apply “”

32

[quote=392279:@Jeff Tullin]Yes.
if you only need the date component, this is a pretty reliable and cross-database method of doing dates
String field holding YYYYMMDD
So 20180415 is 15th April
Sorting this column will sort by date correctly.
ranges are easy
And you can convert to other forms of date for calculation purposes if you need to.

A null here can actually be a null, or you might apply “”[/quote]

But use the standard SQLDATE or SQLDATETIME format

YYYY-MM-DD or YYYY-MM-DD hh:mm:ss

Not only are those formats widely accepted, but Xojo can convert them to/from Xojo Date datatypes automatically

33

@Tony Lam When exactly are you getting this error? Here you say you get a 303 error in your test project:[quote=392162:@Tony Lam]I have setup a simple project, when I tried to set rs.Field(“Date”).value = nil and preform a rs.update, it returns an error: -303 conversion error from string “”

Does this mean there is a bug in Xojo ODBC plugin?

rs.Edit
rs.Field("MyColumn").Value = Nil // sets to NULL in the database
rs.Update

https://drive.google.com/open?id=1xkU7RCccfN4tFXTUgAejLp2XAQuBy8bC [/quote]

I was able to run your exact script steps and update the record set no problem. Is this a database error?

34

[quote=392322:@Jonathan Eisen]@Tony Lam When exactly are you getting this error? Here you say you get a 303 error in your test project:

I was able to run your exact script steps and update the record set no problem. Is this a database error?[/quote]
Just to be clear (for the benefit of the rest of this thread)… You did test this with Firebird as the database engine?
As noted, this does work with some dbs, and not others

35

@Dave S I did not as I don’t have an installation of Firebird on hand and I now reread the post I referenced and have answered my question. Sorry for the confusion, haven’t had my coffee yet.

36

Please try MBS Xojo SQL Plugin and let me know if you find issues.
With SQLDatabaseMBS class, you can simply run SQL commands with prepared statement or do insertRecord which should handle NULL just fine.

37

@Jonathan Eisen Try to update an record with an empty date. Per Dave S, yes, this works with some database engine like MySql.