How to use "start of month" sqlite date modifier using SQLite.swift

Question

Sorry that I can't post any code but I don't even know where to start trying to code this statement using SQLite.swift, I have searched and searched but not found anything that could help:

Edit: (I am trying to avoid RAW SQL but if no option will revert to it)

select date(InvoiceExpDate,'start of month') as month, sum(PaymentAmount) 
from PaySchedules group by month

I have a table named PaySchedules that has a date field (InvoiceExpDate) and a number field (PaymentAmount)

I would appreciate any pointers or code sample.

Thanks!

score 0 · Accepted Answer · answered Mar 21 '17 at 13:26

Since after quite some time there was no option, I will post, just in case this serves someone, the answer using raw sql:

        let select = " select date(PaySchedules.InvoiceDate,'start of month') as month, sum(PaySchedules.PaymentAmount) as amount from PaySchedules join contracts on PaySchedules.Contract_ID = Contracts.ID where invoicedate is not null group by month order by month"

    do{
        let stmt = try db.prepare(select)
        for row in stmt{
            let tmpMonth = row[0] as! String
            var tmpDate = Date()
            tmpDate = dateFormatterYYYYMMDD.date(from: tmpMonth)!
            let tmpAmount = row[1] as! Int64

            let revItemTemp = pnlRevenue() // pnlRevenue Class defined elsewhere
            revItemTemp.pnlRev_Month = tmpDate
            revItemTemp.pnlRev_Amount = Int(tmpAmount)

            revItems.append(revItemTemp) // revItems declared elsewhere as: var revItems: [pnlRevenue] = [] 

        }
    }
    catch{
        //Nothing
    }

Hope this can help someone!

Thanks

How to use "start of month" sqlite date modifier using SQLite.swift

1 Answers1