Query in SQLite with dates

Question

Suppose I have the following student table:

id | name | datecreated
------------------------
1  | john | 5/28/2011
2  | peter| 3/15/2011
3  | luke | 5/19/2011

What would be my Sql statement if I want to select all students created in the month of May and in the year of 2011, regardless of date.

I am using SQLite.

Harag · Accepted Answer · 2011-05-16 11:23:07Z

1

SELECT * FROM tablename WHERE strftime('%Y', datecreated) = '2011' 
                          AND strftime('%m', datecreated) = '5';

answered May 16, 2011 at 11:17

Harag

1,5924 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

V4Vendetta · Accepted Answer · 2011-05-16 11:24:11Z

0

I think you need to use strftime('%Y','yourdatefieldvalue')

This should give you the year part and it takes only string as parameter for the DateTime value (Can't test the query as of now)

answered May 16, 2011 at 11:24

V4Vendetta

38.5k9 gold badges83 silver badges84 bronze badges

Bhavik Goyal · Accepted Answer · 2011-05-16 11:25:05Z

0

Try this

     select * from tablename where (datecreated < date('6/1/2011') and 
datecreated >= date('5/1/2011'))

answered May 16, 2011 at 11:25

Bhavik Goyal

2,7966 gold badges25 silver badges42 bronze badges