Re: working with dates

From: Steve Kass (skass_at_drew.edu)
Date: 07/27/04 

Date: Mon, 26 Jul 2004 22:23:04 -0400

Dion,

  If performance isn't crucial, you can say

where datediff(day,thisDate,thatDate) = 0

Datediff returns the number of day boundaries (midnight moments) between
the two datetime parameters.

If one of the tables has many more rows than the other (let's say the
one with the thisDate column), indexes on the datetime column might be
useful if the query is written this way:

where thisDate >= dateadd(day,0,datediff(day,0,thatDate))
and thisDate < dateadd(day,1,datediff(day,0,thatDate))

or, perhaps more readably,

where thisDate >= convert(char(8), thatDate, 112)
and thisDate < convert(char(8), thatDate + 1, 112)

Steve Kass
Drew University

Dion wrote:

>I am trying to join two tables on a date field. My
>problem is the hour/minute section. I want to match
>07/26/04 on both tables, regardless of the
>hour/minute/second piece of the number. I am used to
>doing this in Access/Excel by getting rid of the decimal
>part of the number, but can't find how to do this in SQL.
>And, I'm embarassed to admit, I wasn't sucessful in
>finding any articles to help.
>
>Thanks
>Dion
>
>