Changing sql server request to sqlite

Question

I am converting a Sql server request to sqlite and I am learning SQLite queries. Regardless of the fact that this is one of the areas with which it is difficult for me to work with it, and these are the dates.

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

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

Field "Event Date" is the accepted date and time format - "YYYY-MM-DD HH: MM: SS.SSS"

Answer 1

DateTime is stored as TEXT in SQLite, so you can simply 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 (as of 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 

Answer 2

Assuming that the values ​​in your table are stored in one of the supported date formats, you can use the date function to extract the part of the timestamp date:

 date(tblSomeTable.EventDate) >= date('now') 

Answer 3

First of all, I would say that you should not use this predicate in SQL Server, because including columns in the expression prevents SQL Server from using any index. If this predicate is the only one in the where clause, then SQL Server will scan the entire table.

However, the predicate can be rewritten as follows:

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

Thus, if there is an index in the EventDate column, SQL Server will use it if it covers the query or if it is sufficiently selective.

The same rule applies to SQLite.

Assuming EventDate is stored as an ISO8601 string. A predicate can be written for SQLite as follows:

 EventDate LIKE date('now') || '%' 

If there is an index in the EventDate column, SQLite can use it.

How are your dates stored in SQLite? Could you post how SQLiteSpy show them?

EDIT:

ABOUT! Sorry I realized that I’m missing the >= . I saw = instead of >= . My answer was correct if the operator was = . For >= my answer should be:

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

For SQL Server. A:

 EventDate >= date('now') 

For SQLite.

I'm getting older, sorry.

Answer 4

Sqlite3 does not have a date type for its Datatype list. The sqlite docs link shows that sqlite has several functions for manipulating time. I would suggest that just converting to unix time as a whole would be your best bet.

Change I would suggest the actual query for a replacement, but I am not so familiar with sql since I am sqlite.

Answer 5

I believe that CL has already answered correctly. You may have saved the values ​​in seconds from the era (i.e.: Skype saves its time and time values ​​in the SQLite <g> table).

 SELECT * from SomeTable where date(eventdate, 'unixepoch', 'localtime') >= date()