SQLite: - find week of the month

Question

I need to extract the week of the month i.e. 1-5 from the date. I am using below formula in python.

df['Week_no_Month']= ((df.DATE.dt.day-1)//7+1)

Now I want this implemented in SQLite.

What is the first week of a month, say if the month starts on a Wednesday? — Joakim Danielson, May 02 '20 at 15:17

score 1 · Answer 1 · answered May 02 '20 at 15:18

If you mean that the week is the day divided by 7, then:

select (strftime('%d', dt.day) + 6 / 7)

SQLite is flexible about mixing strings and numbers, so you don't need an explicit conversion -- although you might want to include that:

select (cast(strftime('%d', dt.day) as int) + 6 / 7)

1 Answers1