CODEWITHSUNDEEP
sqlsqlite
dropWizard
dropWizard

Reputation: 3548

SQL: Query column (string) as date

Assume I have a few columns in a database: id, date_added, tag

`20134` | `February 07, 2019` | `New`
`40323` | `February 09, 2019` | `New`

I want to run a query with a filter based on date_added:

SELECT * FROM table
WHERE date_added > 'February 08, 2019'

How would I do this? I saw you can convert strings to date objects, but wasn't sure if that is possible inside a WHERE filter.

Any help is appreciated!

Edit: I am using SQLite

Upvotes: 1

Views: 1389

Answers (2)

forpas
forpas

Reputation: 164214

You chose a format for the date_added column, that is not comparable.
SQLite is not that flexible with dates, which are in fact Text.
So in this case you need to extract the date parts piece by piece and create a comparable date:

select *
from tablename
where 
  substr(date_added, instr(date_added, ',') + 2) ||
  case substr(date_added, 1, instr(date_added, ' ') - 1) 
    when 'January' then '01'
    when 'February' then '02'
    when 'March' then '03'
    when 'April' then '04'
    when 'May' then '05'
    when 'June' then '06'
    when 'July' then '07'
    when 'August' then '08'
    when 'September' then '09'
    when 'October' then '10'
    when 'November' then '11'
    when 'December' then '12'
  end ||
  substr(date_added, instr(date_added, ' ') + 1, 2) > '20190208'

See the demo

Upvotes: 2

Juanes30
Juanes30

Reputation: 2566

Look, I do not know what database engine you're working with, but in case it's an Sql server, you can do the following:

in the following query it is not necessary to cast the date_added column if it is of the date type, in case it is of the string type, you can also cast it cast (date_add as date).

SELECT * FROM table
WHERE date_added > cast('2019-01-01' as date)

NOTE: it is very important that if they are going to handle dates as string type, they must have a valid format, in Sql Server the dates are saved by default in yyyy-MM-dd -2019-02-10.

If you want to convert the date in another format you can use the convert function

Upvotes: 0

Related Questions