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:
>

compare DateTime values

guys this is kind of a biggie for me.
i've only used sql server to compare the Date Portion of date(s), not the whole string (mm/dd/yyyy hh:mi).
i have a table called Time_Check which has two fields with timestamps,
"TimeLastRun & TimeNextRun". i was trying to compare the Current TimeStamp that i pass from a vb.net form to a stored procedure and compare that DateTime to the TimeNextRun. I'd like to then Update the two Time Columns if rows are found. Should i maybe be using the DateDif Func if i know ahead of time how much time should be elapsed(say five minutes)?
Any Code help is appreciated

Create Procedure dbo.My_Time_Check
@.myDate smalldatetime
as
select alertnumber,alertname,TimeLastRun,TimeNextRun
from time_check
where timeNextRun >= @.myDate
--would like to update these two columns if rows are found

thanks again
this forum is the best
rikHow much time has elapsed? What does that have to do with anything?

Explain why this would not work for you:
Create Procedure dbo.My_Time_Check
@.myDate smalldatetime
as
update time_check
set TimeLastRun = [foo],
TimeNextRun = [bar]
where timeNextRun >= @.myDateYou need to explain your requirments more clearly.|||i appologize - the stress is killing me.

the procedure is called from a service which fires every 60 seconds and passes the current timestamp.
it then goes to a table. what i'm trying to accomplish is keep looking for when five minutes have passed. i dont know what the best way to do that is.
if the time matches, then i want to return the first Column (myAlert) to the Original Calling Procedure and overwrite the TimeStamp(s) Columns.
Mind you, i may only need one TimeStamp column to achieve this. i just dont know at this point.
there'll eventually be several rows - some will need to pass the myAlert Field back after five minutes, some an hour, some a day etc.

myAlert,LastTimeRun,NextTimeRun

i do appreciate the help on this and appologize for not being more clear
thanks again
rik|||...what i'm trying to accomplish is keep looking for when five minutes have passed...Five minutes since what? Really, you need to slow down and explain your problem clearly. The good news is that there are several simple solutions that may apply to your case, but at this point I haven't a clue which one is appropriate.|||sorry about that.
Five minutes since the dateTime that's in the table column "LastTimeRun" and the current_timestamp when the procedure is called.

so if "LastTimeRun" column holds a current Value "2/2/2006 9:30:00"
and i have a Service that fires this procedure every sixty Seconds.
When the Procedure is called it takes the current_timestamp and compares
the datediff between "LastTimeRun" column and the current_timestamp.
if the difference is 5 minutes, then return the First Column and update the "LastTimeRun" column to the current_timestamp that it was just compared to.

.....OR...the column "NextTimeRun" column of that table would hold the actual value -9:35:00 - and i'd just need to compare the current_timestamp
with 9:35 and if they match return the First Column and update the "NextTimeRun" column to 9:40.

currently my table is basically three columns:::

AlertNumber,LastTimeRun,NextTimeRun

hopefully i've done a better job this time of explaining what i'm trying to accomplish. please let me know if you need more. truly, i do appreciate the help.
thanks
rik|||So, why not something along this line:Create Procedure dbo.My_Time_Check
as
update time_check
set TimeLastRun = [foo],
TimeNextRun = [bar]
where timeLastRun <= dateadd(minute, -5, getdate())...though it would seem you would actually want to DO something other than just update the timestamps to make the code actually functional.