0

I have a SQLite DB table which contains Date & time for each item. How do I fetch those records which belongs to current week and which are of the previous week. I tried doing it with datetime() function but could not find a better solution/approach.

I'm trying this for a XAMARIN form based application.

Please do let me know how this can be achived.

Thanks in advance...

Vinayak Hejib
  • 721
  • 7
  • 17

2 Answers2

0

Its possible

Update : Only Last week (excluding the current week)

SELECT * FROM table WHERE strftime('%Y-%m-%d',ItemDate ) >= date('now','-14 days') AND strftime('%Y-%m-%d',ItemDate )<=date('now','-7 days')

Current week

SELECT * FROM table WHERE DATE(ItemDate ) >= DATE('now', 'weekday 0', '-7 days');
Balaji
  • 1,375
  • 1
  • 16
  • 30
  • Thanks.. For current week can I use something like this?, ' SELECT* FROM table WHERE ItemDate >= datetime('now', 'weekday 0', '-7 days'); ' – Vinayak Hejib Jun 13 '16 at 09:32
  • 1
    SQLite don't support date/datetime storage: https://www.sqlite.org/datatype3.html#section_2_2 – jzeferino Jun 13 '16 at 09:32
  • @jzeferino: Since there is no support for storing date in `sqlite`, I'm storing it as `varchar()`.. Now how do I fetch the dates? can I use those above mentioned approaches? – Vinayak Hejib Jun 13 '16 at 09:46
  • There are already answers for this question here: http://stackoverflow.com/questions/14091183/sqlite-order-by-date and here: http://stackoverflow.com/questions/1975737/sqlite-datetime-comparison hope it helps. – jzeferino Jun 13 '16 at 09:50
  • @VinayakGH updated the answer, now u will get the last week excluding the current week – Balaji Jun 13 '16 at 10:05
  • @Balaji: Since there is nno support to store date in sqlite, I'm storing the same in string format and converting back to DATE before executing the query.Is that right? – Vinayak Hejib Jun 13 '16 at 10:18
  • @VinayakGH yes, here's that date which i'm using is `ItemDate`. Dont get confused, updated it.. is this fine? – Balaji Jun 13 '16 at 10:20
0

Use the date() function in your where clause. This example I'm starting at now and getting the last days of the week date('now', 'weekday 6') and then starting now, getting the first day of next week, and -2 weeks date('now', 'weekday 0', '-14 day')

 select field1, field2 from table where 
 datefield < date('now', 'weekday 6') and datefield > date('now', 'weekday 0', '-14 day')
Gary Holland
  • 2,565
  • 1
  • 16
  • 17