I have a SQL database in which i. a. a field 'Date' exists.
I want to determine how often a date falls on e.g. Monday. Is there a direct SQL command for this?
It gives you a number between 0 and 6, 0 being Sunday. If you want the word Monday, you match it to say a map that matches the day number to the day string
B4X:
MyQuery= "SELECT strftime('%w', MyDate) as dday" _
& " FROM table1 "
rs=SQL1.ExecQuery(MyQuery)
Do While rs.NextRow
Log(rs.getint("dday"))
Loop
This line is all messed up. Do While rs.NextRow'rs.Position < rs.RowCount
Use code tags and post again. What does your date look like. The code I posted in post 8 works if you have the right date
Yes, it works in this English format. But since my data is in the database in German format
DateTime.DateFormat = "dd.MM.yy"
and you need it there as well, I will store the date data in a list and use 'DateTime.GetDayOfWeek' to determine each day of the week of a date.
Nonetheless, thanks to everyone. I learned again.
It can still work for you in the Germano format you use.dd.MM.yy, For instance, if your date is: stored German in format: dd.MM.yy, example:: 02.03.22, you can use the Substr( function in SQLite like this to flip the date around:
B4X:
Dim MyQuery As String
Dim rs As ResultSet
MyQuery = $"SELECT Datum, strftime('%w', ('20' || Substr(Datum, 7,2)
||'-'|| Substr(Datum, 4,2)
||'-'|| Substr(Datum, 1,2))) AS dday FROM table1"$
rs=SQL1.ExecQuery(MyQuery)
Do While rs.NextRow
Log(rs.getint("dday")) 'it will show 0, or 1, or 2, etc.
Loop
Yes, that's how it works. I'll have to take a closer look at the string in 'MyQuery' tomorrow. I still have big problems there.
Thank you for your commitment and the example.
Greetings strupp01
Since I also stored the date in the database as a long number, I wanted to try whether the day of the week could also be determined from this. Wrote this code for it
DateTime.DateFormat = "yyyy.MM.dd"
Dim MyQuery As String
Dim rs As ResultSet
MyQuery = $"SELECT Datum_zum_sortieren, strftime('%w', (DateTime.Date(Datum_zum_sortieren))) AS dday FROM Tankvorgang"$
rs=Main.SQL_DB.ExecQuery(MyQuery)
Do While rs.NextRow
Log(rs.getint("dday")) 'it will show 0, or 1, or 2, etc.
Loop