sqlite add x days to date (x stored in table)

Question

I need to do something like this in sqlite (c# app):

select DATE(XDATE,'+XDAYS day') from TABLE

XDATE and XDAYS fields are strings type, stored in "TABLE" table.

someone understood me and could help me? Thank you.

Answer 1

Use the SqlLite DateTime modifiers (https://www.sqlite.org/lang_datefunc.html)

date(timestring, modifier, modifier, ...)

The time string can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows.

NNN days

NNN hours

NNN minutes

NNN.NNNN seconds

NNN months

NNN years

start of month start of year start of day weekday N unixepoch

localtime utc

for instance: SELECT date('now','start of month','+1 month','-1 day');

CREATE TABLE [theDates] (
  [d] VARCHAR(100)  NULL,
  [diff] varchar(100)  NULL
)


insert into [TheDates] VALUES ('now', '-10 day')   

Select d, diff, date([d], [diff]) from [TheDates]


d             diff        date([d], [diff])
-------------------------------------------
now           -10 day     2015-06-02

if you have numeric data

insert into [TheDates] VALUES ('now', -10)   

Select d, diff, date([d], ([diff] || ' day')) from [TheDates]


d             diff        date([d], ([diff] || ' day'))
-------------------------------------------------------
now           -10 day     2015-06-02