Hey there all, Is there some way to tell rails or sql server to ignore the time in a datetime field when doing a comparison? For example, if I do something like select * from users where registration_date = ?, @date where @date might equal some user input like ''03/14/2006'' The result is nothing returned, because in my tables the datetime field looks like this: 3/14/2006 4:04:00 PM I would like to tell rails or sql server to ignore the time, so that these rows will be returned as well. Anyone with any ideas? I''m sure this is something trivial and I''m just not seeing it. Thanks alot! Jin
Hello Jin,> Is there some way to tell rails or sql server to ignore the time in a > datetime field when doing a comparison? > > For example, if I do something like > > select * from users where registration_date = ?, @date > > where @date might equal some user input like ''03/14/2006''Check your SQL Server manual, there must be a way to convert a datetime to a date. In MySQL, sth like : select * from users where date(created_at)="2006-05-03" is possible. -- Jean-Fran?ois. -- ? la renverse.
Hey thanks for the tip Jean. For those that care/want to know, I found out the problem, its the way SQL Server handles datetime variables. Unlike oracle and many other databases, there isnt a to_date or date() function that extracts just the date only. The two ways around this are to convert it to string (with time removed) or to do a BETWEEN mm/dd/yy 00:00:00 (midnight) and mm/dd/yy 12:59:59 which covers the entire day. Jin On 5/3/06, Jean-Fran?ois <jf.web3@gmail.com> wrote:> Hello Jin, > > > Is there some way to tell rails or sql server to ignore the time in a > > datetime field when doing a comparison? > > > > For example, if I do something like > > > > select * from users where registration_date = ?, @date > > > > where @date might equal some user input like ''03/14/2006'' > > Check your SQL Server manual, there must be a way > to convert a datetime to a date. In MySQL, sth like : > > select * from users where date(created_at)="2006-05-03" > > is possible. > > -- Jean-Fran?ois. > > -- > ? la renverse. > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >
Better to use dates in the format of yyyy-mm-dd it''s unambiguous. I think there is even an ISO standard for this. Ross (a colonial that has a date time format of dd-mm-yyyy)
>>>>> "Ross" == Ross Dawson <Ross_Dawson@aas.kaz.com.au> writes:> Better to use dates in the format of yyyy-mm-dd it''s unambiguous. I > think there is even an ISO standard for this.Yes, ISO 8601. I think Sweden is the only country in the world where it''s in common use. -- Calle Dybedahl <calle@cyberpomo.com> http://www.livejournal.com/users/cdybedahl/ "I don''t know what art these programs are state-of; possibly macrame." -- Dr Richard A. O''Keefe, comp.risks
> > Better to use dates in the format of yyyy-mm-dd it''s unambiguous. I > > think there is even an ISO standard for this. > > Yes, ISO 8601. I think Sweden is the only country in the world where > it''s in common use.Far from it: http://www.qsl.net/g1smd/isoimp.htm And I can add Lithuania to that list too. Regards, Rimantas -- http://rimantas.com/
Rimantas Liubertas wrote:>> > Better to use dates in the format of yyyy-mm-dd it''s unambiguous. I >> > think there is even an ISO standard for this. >> >> Yes, ISO 8601. I think Sweden is the only country in the world where >> it''s in common use. > > Far from it: http://www.qsl.net/g1smd/isoimp.htm > And I can add Lithuania to that list too.Not sure what that''s a list of. It has Denmark on there, and I don''t think I''ve ever seen the yyyy-mm-dd format in use here, so it''s probably not a list of countries where it''s actually in common use. -- Jakob Skjerning - http://mentalized.net