1

How can I subtract (or add) days from a dateandtime or smalldatetime date in Web SQL? I have tried searching for it, but couldn't find anything.

The solutions that exist are all for SQL and MySQL.

I have tried: DATEADD (SQL), and DATE_ADD/DATE_SUB (MySQL) functions.

Both are giving errors (even when I copy paste simple example statements), when I use them on my web page.

Patrick
  • 5,526
  • 14
  • 64
  • 101
user3001859
  • 265
  • 1
  • 3
  • 13

1 Answers1

1

Try (change now for te time string, or leave now to add or substract from today)

SELECT date('now','+1 day'); or SELECT date('now','-1 day');

EDIT:

If you want to add or substract to an specific day, you would need to format the date as following

YYYY-MM-DD HH:MM:SS

then you could use date or datetime and add/substract the day. Using the data from the comment:

SELECT DATE('2017-08-24 09:05:00','-1 days') --returns 2017-08-23
SELECT DATETIME('2017-08-24 09:05:00','+1 days'); --returns 2017-08-25 09:05:00
Kevin Miranda
  • 182
  • 1
  • 11
  • It works when I use 'now'. How can I make it work for a smalldatetime object/variable? Suppose I have a variable called max1, which is in the smalldatetime format: '2017-8-24 9:5:00' - how can I add/ subtract from this? – user3001859 Aug 24 '17 at 05:48
  • Thanks a lot, it works when I use a datetime value, but not when I have a smalldatetime value.So I have something like: '2017-8-26 7:15:00' instead of '2017-08-24 07:15:00'. And it returns a null. – user3001859 Aug 25 '17 at 14:28
  • Is there any function I can use on smalldatetime like in the above? [I don't want to use convert/cast because this is part of a nested query like: "SELECT * FROM TABLE WHERE DATE BETWEEN ( nested query for max value - x days ) AND ( max value )"; and I need the output to be in smalldatetime as well ]. – user3001859 Aug 25 '17 at 14:36
  • You might need to convert them, http://www.sqlite.org/lang_datefunc.html you can find the time strings formats. – Kevin Miranda Aug 25 '17 at 20:09
  • Thanks a lot for your help. (Actually I was making a mistake. It was giving error not because it was in 'smalldatetime' format, but because the format I inserted them was incorrect. Strangely this wrong format worked when I used simple queries of searching between dates, but gave error when I tried to perform date functions [adding/subtracting dates] like the above. Now when I have corrected the entries in the db, your code above works perfectly). – user3001859 Aug 27 '17 at 07:16
  • Glad it helped :) – Kevin Miranda Aug 28 '17 at 13:20