Android Question Get weekday from SQL database

[strupp01]

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?

strupp01

 

[DonManfred]

SELECT WEEKDAY(yourdatefield);

?

 

[strupp01]

I get the error message in my B4A program 'no such function: WEEKDAY (code 1 SQLITE_ERROR):'

 

[JoseAlfredo]

-- Return 2 if Monday

SELECT DATEPART(weekday, GETDATE())

 

[strupp01]

I get the error message in my B4A program 'no such function: DATEPART (code 1 SQLITE_ERROR):'

 

[Jeffrey Cameron]

I get the error message in my B4A program 'no such function: DATEPART (code 1 SQLITE_ERROR):'

You're asking for help with SQL which you got, the error message you're showing indicates SQLite, which is not the same as SQL. For SQLite:

SELECT strftime('%w','2018-10-11');

See https://www.sqlitetutorial.net/sqlite-date-functions/sqlite-strftime-function/

 

[strupp01]

OK, that's how it works. Didn't make myself clear enough. Yes, I work with SQLite. Thank you for your help.
Nice day.

strupp01

 

[Mahares]

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

MyQuery= "SELECT strftime('%w', MyDate) as dday" _
        & " FROM table1 "
    rs=SQL1.ExecQuery(MyQuery)
    Do While rs.NextRow    
        Log(rs.getint("dday"))
    Loop

 

[strupp01]

how is rs declared? as cursor ?
rs.NextRow results in syntax errors:
Unknown member: nextrow


My code

Dim MyQuery As String
Dim rs As Cursor


MyQuery= "SELECT strftime('%a', Datum) as dday" _
& " FROM Tankvorgang "
rs=Main.SQL_DB.ExecQuery(MyQuery)

rs.Position = 0
Do While rs.Position < rs.RowCount
Log(rs.getint("dday"))
rs.Position = rs.Position + 1
Loop

Log returns only 0

 

[RB Smissaert]

how is rs declared? as cursor ?
rs.NextRow results in syntax errors:
Unknown member: nextrow


My code

Log returns only 0

rs will be a ResultSet

RBS

 

[DonManfred]

how is rs declared?

ResultSet

 

[strupp01]

OK, I changed
My code :

Dim MyQuery As String
Dim rs As ResultSet


MyQuery= "SELECT strftime('%w', Datum) as dday" _
& " FROM Tankvorgang "
rs=Main.SQL_DB.ExecQuery(MyQuery)

Do While rs.NextRow'rs.Position < rs.RowCount
Log(rs.getint("dday"))
Loop

Log returns only 0 ?
Unfortunately no result

 

[Mahares]

Log returns only 0 ?
Unfortunately no result

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

 

[strupp01]

It probably doesn't work because I saved my date in German format.
DateTime.DateFormat = "dd.MM.yy"

 

[Mahares]

DateTime.DateFormat = "dd.MM.yy"

Try to use this format and test. It will probably work for you: yyyy-MM-dd, exple: 2022-03-02
That is what I use

 

[strupp01]

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.

strupp01

 

[Mahares]

But since my data is in the database in German format
DateTime.DateFormat = "dd.MM.yy"

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:

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

 

[strupp01]

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

 

[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

Unfortunately does not work. Is that possible? If yes, where is the mistake?

 

[strupp01]

OK, also a possibility