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

 

[toby]

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

You didn't specify which SQL database. For mysql

Select WEEKDAY(yourDateColumn) from youyTable
'0=Monday, 1=Tuesday, ...

 

[strupp01]

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

I have SQLite. There is no command there.

 

[Mahares]

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

Of course it will not work. You cannot use DateTime.Date(Datum_zum_sortieren)) inside a SQLite statement like that.
Could you please post your code inside code tags.
Toby, you need to read the entire thread.

 

[strupp01]

Mahares,
you posted me a mini program for determining the day of the week in post #17.
Since I'm not that good at B4A yet, I tried to get a result by entering the date in Long to include year dates from 1980 if necessary.
Like I said, just trying out ignorance. Yes, your previous code works.

   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

 

[RB Smissaert]

Mahares,
you posted me a mini program for determining the day of the week in post #17.
Since I'm not that good at B4A yet, I tried to get a result by entering the date in Long to include year dates from 1980 if necessary.
Like I said, just trying out ignorance. Yes, your previous code works.

   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

I am using Excel dates in my SQLite database and use SQL like this to get the string week days:

select case
when (44623 - 2) % 7 + 1 = 1 then 'Monday'
when (44623 - 2) % 7 + 1 = 2 then 'Tuesday'
when (44623 - 2) % 7 + 1 = 3 then 'Wednesday'
when (44623 - 2) % 7 + 1 = 4 then 'Thursday'
when (44623 - 2) % 7 + 1 = 5 then 'Friday'
when (44623 - 2) % 7 + 1 = 6 then 'Saturday'
else 'Sunday' end as day

44623 as an Excel day is today's date.
I think you are using Julian days, but you will get the idea.

RBS

 

[strupp01]

Thanks, but I'll stick with Mahares' solution. I can live with that.
Thanks again everyone. I hope I didn't annoy you too much.?

 

[Mahares]

Thanks again everyone. I hope I didn't annoy you too much.

If your date is in ticks, try this code and see it works for you. Don't forget he division by 1000, see code:

MyQuery = $"Select strftime('%w', DateTime(Datum_zum_sortieren/1000, 'unixepoch', 'localtime')) as dday"$
    rs=SQL1.ExecQuery(MyQuery)
    Do While rs.NextRow
        Log(rs.getint("dday")) 'it will show 0, or 1, or 2, etc.
    Loop

 

[strupp01]

in the hurry you didn't specify the table in the command. After I entered this, it worked.

MyQuery = $"Select strftime('%w', DateTime(Datum_zum_sortieren/1000, 'unixepoch', 'localtime')) as dday from Tankvorgang"$

Thanks again. I would never have been able to do this on my own. It's great to have a forum like this with members like this