Changing a sql server query to sqlite

Question

I'm converting a Sql Server query to sqlite and I am in the process of learning the SQLite queries. Regardless this is the one area that I am having a hard time with and that is the dates.

DATEADD(dd, 0, DATEDIFF(dd, 0, tblSomeTable.EventDate)) >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

What would the equivalent to this in sqlite be? Thank you in advance.

The Event date field is a accepted datetime format - "YYYY-MM-DD HH:MM:SS.SSS"

Answer 1

DateTime is stored as TEXT in SQLite, so you can just compare strings:

CREATE TABLE tblSomeTable (EventDate TEXT);
INSERT INTO tblSomeTable (EventDate) VALUES
    (strftime('%Y-%m-%d %H:%M:%f', 'now', '-1 day')),
    (strftime('%Y-%m-%d %H:%M:%f', 'now'          )),
    (strftime('%Y-%m-%d %H:%M:%f', 'now', '+1 day'));

SELECT EventDate,
       substr(EventDate, 1, 10),
       strftime('%Y-%m-%d', 'now'),
       substr(EventDate, 1, 10) >= strftime('%Y-%m-%d', 'now')
FROM tblSomeTable;

SELECT EventDate,
       substr(EventDate, 1, 10),
       date(),
       substr(EventDate, 1, 10) >= date()
FROM tblSomeTable;

The result in both cases (at the date of this edit :-) would be:

2016-01-14 12:34:56.789|2016-01-14|2016-01-15|0
2016-01-15 12:34:56.789|2016-01-15|2016-01-15|1
2016-01-16 12:34:56.789|2016-01-16|2016-01-15|1