Hello group, I need to convert my “old” queries for MsAccess to queries for SQLite…Ex: go from checking the year … from … where Year(Data)=2024 … . to … where strftime(‘%Y’,Data)=‘2024’ . Is it correct?
looks ok
you can make a query to see the result in db browser for sqlite app.
SELECT strftime('%Y',MyDateField) from MyTable
SELECT strftime('%Y',MyDateField) from MyTable where strftime('%Y',MyDateField) = '2024'
IF your dates are stored in the ANSI format of YYYY-MM-DD, then the query would be
select data like ‘2024%’
I have the database with the dates stored as dd/mm/YYYY, what changes?
I have the database with the dates stored as dd/mm/YYYY
add a new field and make a update query and cast/convert the old format into the new format you need.
Is there any way to write a query that will identify the year of a string written as dd/mm/yyyy ?
In Access it is possible, in SQLite not? or write a query that identifies the 7-8-9-10th number of the string dd/mm/yyyy in order to extrapolate the year and compare it with 2024?
Ok I went to dig around a bit of information on query management in the sql language and I found this, I could write: "where substr(Data,7,4)=‘2024’ … in practice I extrapolate the data YYYY from the Data string, from the 7th position of the string that interests me dd/mm/YYYY and I count 4 characters YYYY. In the comparison I will therefore have 2024=2024. Right?
https://www.sqlite.org/lang_corefunc.html#substr
Looks like as long as you have always dd/mm/yyyy it should work but we don’t know if any of your dates are 1/1/2024 (for example), in that case you will not get a match.
Looks like you don’t want to update your db and change from dd/mm/yyyy to SQLDate format.
The dates are all in the format dd/mm/yyyy, also because in the database they are all already like this, but also in the insertions there are controls so that the data is always inserted in the format dd/mm/yyyy. When I have time to dedicate, I will try to convert the existing dates into the sql format and therefore also the fields for the insertions. But if it works now I will go ahead because I would like to finish at least the majority of the project.
dd/mm/yyyy sometimes refers to 01/01/2024 but sometimes allows 1/1/2024 (without the 0). That is what I’m trying to say.
If your dd/mm/yyyy force the 0 on a 1 digit day/month then you should not have problems. If the 0 is not enforced or not there in the actual database then you may have problems.
Yes yes, I understand very well, I repeat, I made sure that the dates that are inserted are analyzed, and therefore that if you insert dates in the format d/m/yy, they are still written as dd/mm/yyyy
1 Like
You can also write your query as "LIKE ‘%2024’, but it is highly suggested you convert the data to SQLDate format. It will make so many things much easier.
3 Likes
Frederico,
I created and shared an example for you doing that: curency, European dates (with sorting).
If you do not understand the code ask, but repeating the same questions (or errors as I do for some days while creating the example) will let you on the face of the front door… 