Using SQL to set a FMP date field to ''

[cincin]

Hello,

I'm using the MBS FileMaker plugin to run some SQL queries on my FileMaker 10 file. I am not using an external data source, the SQL acts directly on the tables in my FMP file.

One oddity with SQL in FileMaker is the syntax surrounding date and time fields where you have to enclose the field name in quotes and the value in curly brackets {} like so:

MBS("FM.ExecuteSQL"; "INSERT INTO table_a ("date", "time") VALUES ({" & table_b::date & "}, {" & table_b::time & "})")

This query works as long as the date and time fields of table_b are not empty. That is to say that the following query works:

INSERT INTO Untitled ("date", "time") VALUES ({10/06/2009}, {9:00:00})

but the following query fails:

INSERT INTO Untitled ("date", "time") VALUES ({}, {9:00:00})

I have tried putting in a zero if the date is blank but it actually injects a zero time into my date field... like 0:00:00

I know that I can achieve that while composing the SQL query with FMP calcs by making the date and time fields conditional on them having a value but that complicates my queries quite a bit and makes it all much harder to read, plus there has to be a proper way to tell FMP to set a date field to '' using a SQL query.

Any ideas ?

Thanks!

[Vaughan]

Try {""}.

[cincin]

Hello,

Thanks for the reply.

I tried the double quotes and it didn't work. I also tried a single space within a single and double quotes, still no go.

But THEN

I tried and empty single quote WITHOUT the curly brackets and it worked! yay

So for future reference, for posterity:

INSERT INTO Untitled ("date", "time") VALUES ('', {9:00:00})

does the job.