comparing dates

Hi all,
I'm trying to compare two dates, from two tables. Both are of datetime
datatype.
I dont want the time portion to be involved in the date, as I only need to
compare days. Having the datetime in the comparison yeilds unexpected
results
Thanks
RobertRobert Bravery wrote:
> Hi all,
> I'm trying to compare two dates, from two tables.
In a join statement?

> Both are of datetime
> datatype.
> I dont want the time portion to be involved in the date, as I only
> need to compare days. Having the datetime in the comparison yeilds
> unexpected results
>
You could simply use CONVERT on both datetime columns:
... CONVERT(char(8),table1.datecol,112) =
CONVERT(char(8),table2.datecol,112)
but that would prevent an existing index from being used on both tables
resulting in poor performance. An alternative that may perform better,
especially if an index exists for the datetime coumn in table 1, would be
this:
... table1.datecol >=dateadd(d,datediff(d,0,table2.datecol),0) and
table1.datecol < dateadd(d,1 + datediff(d,0,table2.datecol),0)
HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Convert the dates to a ISO date, thats my best practise for this.
CONVERT(VARCHAR(10),GETDATE(),112)
HTH, jens Suessmeyer.|||Compare after taking off the time part of DATETIME field...
SELECT getdate()
SELECT CAST(FLOOR(CAST( getdate() AS float)) AS DATETIME)
Thanks,
Sree
"Jens" wrote:

> Convert the dates to a ISO date, thats my best practise for this.
> CONVERT(VARCHAR(10),GETDATE(),112)
> HTH, jens Suessmeyer.
>|||Hi Bob,
Thanks for the response.
Could I well use, youre response gave me an idea, if
convert(int,@.dol,112)>=convert(int,@.sdate+1,112)
@.dol and @.sdate are both datetime datatypes
Would that comparison work
Thanks
Robert
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OCeiNByKGHA.536@.TK2MSFTNGP09.phx.gbl...
> Robert Bravery wrote:
> In a join statement?
>
> You could simply use CONVERT on both datetime columns:
> ... CONVERT(char(8),table1.datecol,112) =
> CONVERT(char(8),table2.datecol,112)
> but that would prevent an existing index from being used on both tables
> resulting in poor performance. An alternative that may perform better,
> especially if an index exists for the datetime coumn in table 1, would be
> this:
> ... table1.datecol >=dateadd(d,datediff(d,0,table2.datecol),0) and
> table1.datecol < dateadd(d,1 + datediff(d,0,table2.datecol),0)
> HTH,
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Robert Bravery wrote:
> Hi Bob,
> Thanks for the response.
> Could I well use, youre response gave me an idea, if
> convert(int,@.dol,112)>=convert(int,@.sdate+1,112)
> @.dol and @.sdate are both datetime datatypes
I thought we were dealing with columns, not variables ... ?

> Would that comparison work
>
Did you try it? If you had, you would have answered your own question.
Yes, that will work, but the ",112" part will be ignored. When converting
the datetime to int, you will get the number of days since the seed date,
run this script to see:
select convert(int,getdate(),112),convert(int,g
etdate())
Steve Kass pointed out to me a while back that there is a performance impact
involved in converting a datetime to another datatype, which is why I used
the dateadd(d,datediff ... technique in my post, which Steve showed to be
faster.
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||HI Bob
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message news:%
> I thought we were dealing with columns, not variables ... ?
We are and we arent. I need to compare a few things, all involve variables
and columns, and a combination of them

> Did you try it? If you had, you would have answered your own question.
I did. ANd it did seem to work. Just wanted some confirmation that I was
doing the right thing or doing t the right way.

> Yes, that will work, but the ",112" part will be ignored. When converting
> the datetime to int, you will get the number of days since the seed date,
> run this script to see:
> select convert(int,getdate(),112),convert(int,g
etdate())
> Steve Kass pointed out to me a while back that there is a performance
impact
> involved in converting a datetime to another datatype, which is why I used
> the dateadd(d,datediff ... technique in my post, which Steve showed to be
> faster.
>
Thanks FOr this
Robert|||Thanks
Robert
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:48FD7950-C281-4B31-BB69-B38C76A77EC2@.microsoft.com...
> Compare after taking off the time part of DATETIME field...
> SELECT getdate()
> SELECT CAST(FLOOR(CAST( getdate() AS float)) AS DATETIME)
>
> Thanks,
> Sree
>
> "Jens" wrote:
>