Return date as year

I wrote this little search query:

 User.where("strftime('%Y', birthday) = ?", 1994)

But somehow i get no search resuts although i now that i have several users with their birthday in 1994. In the Users Table birthday is defined as date

Now i have two ideas how i could solve my problem:

1.I could check what the result of "strftime('%Y', birthday) is for a specific user or the whole user collection. But i dont know the expression! Maybe you know the expression?

2.You know what i did wrong and you have an answer?

Thanks!

Answers


In Rails you can write the query like:

User.where("birthday BETWEEN '?-01-01' AND '?-12-31'",1994,1994)

See this post for why this kind of format is good performance-wise when compared to something that uses "YEAR": SQL query where() date's year is $year.


Your mileage may vary but I've gotten around some of these issues by querying the date column using it's underlying string format...

User.where("birthday like ?", "1994-%")

I'm too lazy to checkout sqlite's support for regexen but if you can use them then you could also try

User.where birthday: /^1994-\d{2}-\d{2}/

you should use something like User.where("YEAR(birthday) = ?", 1994) Ref: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_year


Need Your Help

What does Java byte code look like?

java binary jvm byte bytecode

On the Wikipedia page http://en.wikipedia.org/wiki/Java_bytecode#Instructions it says Java byte code looks like this: