SQLDateTime type mismatch

Targets
1

I need help with this code:

Try
  Var updatedOn As  DateTime 
  updatedOn = row.Column("UpdatedOn").DateTimeValue
  // Do something...
Catch e As TypeMismatchException
  System.DebugLog("Error on converting " + row.Column("UpdatedOn").StringValue + " to DateTime")
End Try

It will produce this output:

             Error on converting 2022-01-15 11:17:29.148 to DateTime
             Error on converting 2022-02-03 08:50:42.413 to DateTime
             Error on converting 2022-02-03 08:51:34.482 to DateTime

On my database UpadedOn column is maintained by a trigger like this

CREATE TRIGGER IF NOT EXISTS Document_After_Update_Trigger
AFTER INSERT ON Document
BEGIN
   UPDATE Document SET UpdatedOn = STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW') WHERE id = NEW.id;
END;

I don’t find explanation on the documentation. Please can somebody help me ?

2

SET UpdatedOn = STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')

If you are storing the date time as a string, maybe do the conversion when you read it back
Something like:

var the_db_value as string
the_db_value= row.Column("UpdatedOn").stringvalue
 updatedOn =  DateTime.FromString(the_db_value, Locale.Current, TimeZone.Current)
3

Everything is string on SQLite…

The code you sent return:

Exception Message: Date is not in an accepted format for parsing

4

What wrote those dates? They’re not the SQLDateTime format that Xojo uses. It would be more prudent to find out why the dates are being written wrong than it would be to patch them to readability every time.

1 Like
5

Thank you @Tim_Parnell I need nano seconds in my db so I will do things differently without using DateTimeValue

For those who don’t need nano seconds you can must use a trigger like this :

CREATE TRIGGER IF NOT EXISTS Document_After_Update_Trigger
AFTER INSERT ON Document
BEGIN
   UPDATE Document SET UpdatedOn = STRFTIME('%Y-%m-%d %H:%M:%S', 'NOW') WHERE id = NEW.id;
END;
6

sqlite have a Date & Datetime method you can put around a field
SELECT date(mystringfield) As mydate
https://www.sqlite.org/lang_datefunc.html

1 Like
7 
Var updatedOn As DateTime = DateTime.FromString(row.Column("UpdatedOn").StringValue)

Will ignore the fractional seconds

1 Like