Good evening group, a tip on dates in SQLite … I’m switching (as I can) from Ms Access management to Sqlite, and I’m finding some differences with the management of dates. I’m interested in dates in the format dd/mm/YYYY, I thought about the following … set a preformatting with the ValidationMask of the textboxes with ##/##/####. Then for each sequence of data entered check if they are numbers and finally, if they are numbers check that it is a valid date. Then archive it in the format dd/mm/YYYY. I would like to create a global Method, to be applied on each insertion. Is this the right way? Only in one case the data is stored like this: dd/mm/YYYY HH:MM:SS, in this case how do I manage the date? I’m not interested in HH:MM:SS. (25/10/2024 09:10:54). Can I still manage the dates with a general method and possibly intercept this exception?
You shouldn’t store dates as dd/mm/yyyy.
At least you should store them as SQLDate YYYY-MM-DD
Use a prepared statement. Xojo will accept a DateTime and store it for you. Storing dates in your localized string format is inadvisable.
the Xojo DateTime object have a SQLDateTime and SQLDate Propertie.
sqlite Date And Time Functions
useful for querys
The problem is that the database was created many years ago and the dates stored are all in the format dd/mm/YYYY.
Ok, but the database already has all the dates stored like this.
Then your first job is to add a new column to the database, and write a small program which copies those old dates into the new column in the TEXT format YYYY-MM-DD. Then delete the old column and rename the new column.
In fact, you wouldn’t need to write a program to do it. All of that could be done using the sqlite3 CLI. Moving a database to another platform is the best time to correct previous design erors.
Ok I’ll see how to fix this problem.
Did you take the example project I build for you 3 weeks ago ?
It stored and retrieve the dates…
Yes thanks, I saw it and studied it… I managed to solve the problem.
A bit late to the party (I dont check in often).
I handle all date time stuff as Julian date - ie a double. It is soooo much easier than mucking around with days/months/years and whether or not it’s a leap year and all that. Differences between dates/times, date +/- days or time etc are then trivial.
It’s also easy to validate user input by converting to JD and back again, if the resulting string doesn’t equal what was given, it was invalid.
Look up Jan Meeus Astronomicalk Algorithms for the routines to/from JD, they’re trivial.