Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

Compare Datetime Values in SQL

I need to compare two datetime values in my SQL database to determine which is the later time.

The following code is not working:

If objRS("LastUpdate") > request.form("FormStart") then
Response.write "This ticket has been updated by another user since the time you accessed it. You will need to check on the updated status of this ticket."
Response.End
End if

I knew this had to be too simple.  How would I compare these two datetime values?

Thanks.
0
Rougie
Asked:
Rougie
1 Solution
 
mberumenCommented:
use the datediff function...

0
 
mberumenCommented:
for example if you have two dates.. depending on how accurate you want to be you can get the difference in seconds, minutes, hours,days, etc

If (datediff("s",request.form("FormStart"),objRS("LastUpdate")) > 0 then  'form date is after recordset date
   ' add code here
else
   'add code here
end if
0
 
RougieAuthor Commented:
The datediff function looks like it only compares whether or not the two datetime values are different, not whether or not one is actually later than the other.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
keystrokesCommented:
mberumen is right.  If the result is > 0 then request.form("FormStart") is smaller than objRS("LastUpdate"), and vis versus for a negative result.
0
 
mberumenCommented:
it will return the difference between both dates in the specified unit.  so a positive value means that dateA is after dateB, whereas a negative value means that dateA is before DateB.

Perhaps I have  a typo on my code above, try this and check the results


Response.write "formdate - recorddate=" & datediff("s",request.form("FormStart"),objRS("LastUpdate"))

Response.write "recorddate - formDate=" & datediff("s",objRS("LastUpdate"),request.form("FormStart"))

You should see two different values (unless both dates are the same or they are dates only (no time part), in which case you need to use the days difference

Response.write "formdate - recorddate=" & datediff("d",request.form("FormStart"),objRS("LastUpdate"))

Response.write "recorddate - formDate=" & datediff("d",objRS("LastUpdate"),request.form("FormStart"))





0
 
apolloisCommented:
Hi Rougie,

>>>If objRS("LastUpdate") > request.form("FormStart") then

Simply convert the form data to date/time type:

If objRS("LastUpdate") > CDate(request.form("FormStart")) then


Of course, you really should check for a blank or non-date type:

strFormStart = request.form("FormStart")

IF VarType(strFormStart) = vbDate THEN

     If objRS("LastUpdate") > CDate(strFormStart) THEN
     '... remainder of your code here

ELSE

     '--- FORM INPUT ERROR ---
     'Display error msg, and redisplay FORM

END IF


Best Regards,
>apollois<
0
 
RougieAuthor Commented:
You were right.  My confusion.  There's 50 extra points for you.
 
Thanks.
0
 
mberumenCommented:
Thanks, glad to be able to help..

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now