0

I have stored my date and time in database in following format and as TEXT.

MM/dd/yyyy HH:mm:ss

I am trying to select all records ONLY from yesterday by doing this:

SELECT * from source_tbl where date_time = (select max(date_time) 
   from source_tbl 
   WHERE date_time < DATE('now', '-1 day') )

I know that if I only store MM/dd/yyyy in database, then my above query will work fine, but in my situation I have HH:mm:ss so it is not going to work.

But is there any way to get only MM/dd/yyyy portion and then apply my above query, or any way of doing this?

nice guy
  • 151
  • 1
  • 14
  • `MM/dd/yyyy` alone does not work either. As shown in the duplicate question, comparisons work only with a supported date format. Also see [this](http://stackoverflow.com/questions/26108827/sqlite-date-is-greater-than-other-date-e-g-25-09-2014-doesnt-work) and [that](http://stackoverflow.com/questions/21159031/sort-date-stored-in-sqlite-database). – CL. Oct 01 '14 at 07:50
  • @CL. Last two questions. **1.** If I change the format to `yyyy-MM-dd HH:mm:ss` then still there is no chance to select all records from Yesterday? **2.** Or if I change the datatype of date_time column to **Date**, then it is possible to get the date part and apply my above query? – nice guy Oct 01 '14 at 08:51
  • 1
    1. Of course there is. 2. SQLite [has no data types](http://www.sqlite.org/datatype3.html). – CL. Oct 01 '14 at 09:13
  • 2
    To help anyone else, I tried this: `SELECT * FROM source_tbl WHERE strftime('%Y-%m-%d', date_time) = date('now','-1 day')` and it is working for me. Also I have changed the format to `yyyy-MM-dd HH:mm:ss` – nice guy Oct 01 '14 at 11:40

1 Answers1

-1

You need to convert both your data and your value into time-less form for the comparison. Something along these lines will probably work:

SELECT * FROM source_tbl
WHERE date(date_time) = date('now', '-1 day');

That will select all records from yesterday. Your original query confused me as to whether you want just one record from yesterday or all of them, but you should be able to add criteria to that query if you need to do.

John Bollinger
  • 160,171
  • 8
  • 81
  • 157