# (basic sql) search datetime column by date

**URL:** <https://discuss.rubyonrails.org/t/basic-sql-search-datetime-column-by-date/26585>\
**Category:** rubyonrails-talk\
**Created:** [March 20, 2008, 2:44am UTC](https://discuss.rubyonrails.org/t/basic-sql-search-datetime-column-by-date/26585 "2008-03-20T02:44:08Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![jko170](https://avatars.discourse-cdn.com/v4/letter/j/d07c76/32.png) [@jko170](https://discuss.rubyonrails.org/u/jko170)\
**Post date:** [March 20, 2008, 2:44am UTC](https://discuss.rubyonrails.org/t/basic-sql-search-datetime-column-by-date/26585/1 "2008-03-20T02:44:08Z")

</div>

I have a datetime column and I need to search by a date, not datetime.

So obviously, I cannot do this:

find(:all, :conditions =\> ['my\_datetime\_column = ?', Date.today])

All I want to do is find the records on a datetime column that match today's date.

What I have had to do is convert each record in the datetime column to a date and compare (== Date.today), which is slower than just using a conditions option. What do you guys suggest?

Thanks for any help!

---

<div class="post-metadata">

**Author:** ![Frederick\_Cheung](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/frederick_cheung/32/3901_2.png) [@Frederick\_Cheung](https://discuss.rubyonrails.org/u/Frederick_Cheung)\
**Post date:** [March 20, 2008, 8:15am UTC](https://discuss.rubyonrails.org/t/basic-sql-search-datetime-column-by-date/26585/2 "2008-03-20T08:15:30Z")

</div>

> I have a datetime column and I need to search by a date, not datetime.
> 
> So obviously, I cannot do this:
> 
> find(:all, :conditions =\> ['my\_datetime\_column = ?', Date.today])
> 
> All I want to do is find the records on a datetime column that match today's date.

You want to say ['my\_datetime\_column \>= ? and my\_datetime\_column \< ?',  
Date.today(), Date.today()+1] This works because the dbs I know of handle '2007-04-20' as being  
'2007-04-20 00:00:00'. If you db does something different you'll have  
to adjust for that.

Fred

---

<div class="post-metadata">

**Author:** ![jko170](https://avatars.discourse-cdn.com/v4/letter/j/d07c76/32.png) [@jko170](https://discuss.rubyonrails.org/u/jko170)\
**Post date:** [March 20, 2008, 3:28pm UTC](https://discuss.rubyonrails.org/t/basic-sql-search-datetime-column-by-date/26585/3 "2008-03-20T15:28:08Z")

</div>

Works perfectly! Thank you!

---

<div class="post-metadata">

**Author:** ![Michael\_Steinfeld](https://avatars.discourse-cdn.com/v4/letter/m/bc79bd/32.png) [@Michael\_Steinfeld](https://discuss.rubyonrails.org/u/Michael_Steinfeld)\
**Post date:** [March 20, 2008, 4:27pm UTC](https://discuss.rubyonrails.org/t/basic-sql-search-datetime-column-by-date/26585/4 "2008-03-20T16:27:40Z")

</div>

You can also use CURDATE() if you are using MySQL

Model.find(:all, :conditions =\>['created\_on \<= CURDATE()'], :limit =\> 10)

Model.find(:all, :conditions =\> ['my\_datetime\_column = CURDATE()'])

---

<div class="post-metadata">

**Author:** ![jko170](https://avatars.discourse-cdn.com/v4/letter/j/d07c76/32.png) [@jko170](https://discuss.rubyonrails.org/u/jko170)\
**Post date:** [March 20, 2008, 10:44pm UTC](https://discuss.rubyonrails.org/t/basic-sql-search-datetime-column-by-date/26585/5 "2008-03-20T22:44:06Z")

</div>

Ah, it is not working as expected. I have a record with the datetime of 7pm last night (central time). I'm using tztime so when I do this:

Model.find(:all, :conditions =\> ['datetime\_column \>= ? and datetime\_column \< ?', TzTime.now.to\_date, TzTime.now.to\_date + 1])

It is showing up in the records for today when it should be yesterday. Is there maybe a way I can just search the date in the datetime column?

---

<div class="post-metadata">

**Author:** ![jko170](https://avatars.discourse-cdn.com/v4/letter/j/d07c76/32.png) [@jko170](https://discuss.rubyonrails.org/u/jko170)\
**Post date:** [March 21, 2008, 6:45am UTC](https://discuss.rubyonrails.org/t/basic-sql-search-datetime-column-by-date/26585/6 "2008-03-21T06:45:00Z")

</div>

Woops! Nevermind. It is a timezone problem not a query problem.
