Sql server, date and time


#1

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


#2

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.


#3

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