1

I am trying to fetch 30 days back results from today in order to see who have not paid last month.

I tried something 

SELECT * FROM MemberInformation WHERE DateOfAd = 'DATEADD(Day,-30,getdate())'

and query successfully executed but did not fetch the values I wanted.

My database:

enter image description here

please guys help me out...

M. Prokhorov
  • 3,894
  • 25
  • 39
yns Ahmed
  • 29
  • 1
  • 6

2 Answers2

1

DATEADD is not work in sqlite you but you can use its sqlLite equivalent DateTime like code below : 

select * from TableName where DateColumn < DateTime('Now', 'LocalTime', '-10 Day');

http://www.sqlite.org/lang_datefunc.html

SQLite equivalent of SQL Server DateAdd function

Amir Hossein Mirzaei
  • 2,325
  • 1
  • 9
  • 17
  • hi Amir sir, I tried but it fetch value of 2-10-2018 and 2-20-2018 . im sending screenshot sir kindly have a looks [link](https://ufile.io/1x7dr) – yns Ahmed Apr 21 '18 at 08:56
  • @user9587628 you can change '-10 Day' to '-30 Day' and it returns data from 30 days ago – Amir Hossein Mirzaei Apr 21 '18 at 09:01
  • no sir i tried it ...if i change from -10 to -30 it not show any results. sir can you please connect by teamviewer if possible , or may i have your contact – yns Ahmed Apr 21 '18 at 09:03
  • @user9587628 what is your date look like ? give me some sample you are trying to fetch – Amir Hossein Mirzaei Apr 21 '18 at 09:10
  • it shows the result of 02-10-2018 and 02-20-2018 I attach screenshot sir on the above comment click on the link sir u can check it. – yns Ahmed Apr 21 '18 at 09:18
  • sir i tried something this `select Name from MemberInformation where DateOfAd = date ('now','-1 month')` to see name of on month back result . query get executed but value not fetch is there anything wrong – yns Ahmed Apr 21 '18 at 09:21
  • @user9587628 your date format i think is a wrong your date should looks like this yyyy/mm/dd – Amir Hossein Mirzaei Apr 21 '18 at 09:22
1

Try 

SELECT * FROM MemberInformation WHERE DateOfAd  BETWEEN datetime('now', '-30 days') AND datetime('now', 'localtime');

or Try to change where condition as,

WHERE DateOfAd  BETWEEN datetime('now', 'localtime', '-30 days') AND datetime('now', 'localtime');
Jyoti JK
  • 2,141
  • 1
  • 17
  • 40
  • thank your but it not fetch any value from database but query execute successfully – yns Ahmed Apr 21 '18 at 08:52
  • try #Jyoti Query and just remove `localtime` see this Query **`SELECT * FROM MemberInformation WHERE DateOfAd BETWEEN datetime('now', '-30 days') AND datetime('now');`** @user9587628 – Ali Apr 21 '18 at 09:40