# (basic sql) search datetime column by date

**URL:** <https://www.ruby-forum.com/t/basic-sql-search-datetime-column-by-date/132429>\
**Category:** Rails\
**Created:** [March 20, 2008, 3:44am UTC](https://www.ruby-forum.com/t/basic-sql-search-datetime-column-by-date/132429 "2008-03-20T03:44:55Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![jko170](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/d07c76/32.png) [@jko170](https://www.ruby-forum.com/u/jko170)\
**Post date:** [March 20, 2008, 3:44am UTC](https://www.ruby-forum.com/t/basic-sql-search-datetime-column-by-date/132429/1 "2008-03-20T03:44:55Z")

</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:** ![jko170](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/d07c76/32.png) [@jko170](https://www.ruby-forum.com/u/jko170)\
**Post date:** [March 20, 2008, 9:16am UTC](https://www.ruby-forum.com/t/basic-sql-search-datetime-column-by-date/132429/2 "2008-03-20T09:16:26Z")

</div>

On 20 Mar 2008, at 02:44, jko170 wrote:

> 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://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/d07c76/32.png) [@jko170](https://www.ruby-forum.com/u/jko170)\
**Post date:** [March 20, 2008, 4:29pm UTC](https://www.ruby-forum.com/t/basic-sql-search-datetime-column-by-date/132429/3 "2008-03-20T16:29:34Z")

</div>

Works perfectly! Thank you!

On Mar 20, 3:15 am, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![jko170](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/d07c76/32.png) [@jko170](https://www.ruby-forum.com/u/jko170)\
**Post date:** [March 20, 2008, 5:28pm UTC](https://www.ruby-forum.com/t/basic-sql-search-datetime-column-by-date/132429/4 "2008-03-20T17:28:31Z")

</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://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/d07c76/32.png) [@jko170](https://www.ruby-forum.com/u/jko170)\
**Post date:** [March 20, 2008, 11:44pm UTC](https://www.ruby-forum.com/t/basic-sql-search-datetime-column-by-date/132429/5 "2008-03-20T23:44:24Z")

</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?

On Mar 20, 11:27 am, “Michael S.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![jko170](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/d07c76/32.png) [@jko170](https://www.ruby-forum.com/u/jko170)\
**Post date:** [March 21, 2008, 7:45am UTC](https://www.ruby-forum.com/t/basic-sql-search-datetime-column-by-date/132429/6 "2008-03-21T07:45:49Z")

</div>

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