Solved

Comparing two dates - Access 2010

Posted on 2013-06-17
8
348 Views
Last Modified: 2013-06-26
Here is my simple query-

SELECT HireDates.[Person Number], HireDates.Name, HireDates.[Assigned To Org Id]
FROM HireDates
WHERE (("HRMSLDate"="HCMLHDate"));

Its returning the whole data from the table, where I am doing wrong in the syntax? Please help, both date fields are in Date/Time(HRMSLDate and HCMLHDate)

Thanks again
0
Comment
Question by:gtmathewDallas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 

Accepted Solution

by:
gtmathewDallas earned 0 total points
ID: 39254593
Thanks I got it, I used DateValue with the fields, Thanks
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39255729
Hi,

you don't need DateValue, the syntax in the WHERE-part is:

WHERE HRMSLDate = HCMLHDate

Open in new window


If you use a function to convert the value the risk is high that an index is not used and therefore the performance is slower.

Cheers,

Christian
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255785
[DateValue] Returns a Date value containing the date information represented by a string, with the time information set to midnight (00:00:00).
http://msdn.microsoft.com/en-us/library/6d6k22a5(v=vs.80).aspx

To me this indicates your date/time information does in fact have "time" in it, hence the chances that  2 values are equal is slight, and that's why you existing query isn't working as expected.

using DateValue() appears to be a common method to overcome this because it returns a date at midnight. However it is returning a string and string comparisons are slower than numeric comparisons.

another alternative is (I believe):
 CDate(Cint(<<date-field-here>>))

Cint() removes the fractional part of the date/time, CDate then re-converts to date

appears to be that Cint() by itself could be used and would be just as effective.

e.g.

WHERE Cint(HRMSLDate) = Cint(HCMLHDate)

A DateTime value is internally stored as a Double value. The integer part are the days since 12/30/1899. The fractional part is the time of the day (0.5 is high noon, 0.75 is 6 PM, etc). So, if you cut the fractional part using the Int() function, the time  is "cut", or as correctly said by others already, set to midnight.
Access (SQL) is not as strict in type checking as VB is. In other words, it does implicit conversions if necessary, therefore you can use the Int() function and pass a DateTime value.
http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/bc47d511-3873-4e04-baac-aa77792a18b2

nb: I am not a current user of Access and unable to test this.
0
Stressed Out?

Watch some penguins on the livecam!

 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39255883
Hi,

normally you don't need to cut the time part because if you only saved a date value the time value is always "00:00:00" (or in other words: The part after comma is always 0). So if the value in the form has for example a default value "Date" and not "Now" there is no need to convert any value.

The syntax above is wrong because the column names are in double quotes.

Cheers,

Christian
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255905
>>normally you don't need to cut the time part
agreed, but if you do, then HRMSLDate = HCMLHDate wouldn't produce desired results.

given the data is hire dates - I would have thought it unlikely time was a factor
but: if DateValue is working, then time could be the cause

(or, by applying DateValue, the syntax has been corrected perhaps?)
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39256054
Hi,

you're right, but that is a world of guesses, only the author can tell us the details to answer these questions. I think he does have enough information now to correct his query accordingly.

Cheers,

Christian
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39256158
Looking at the question, it seems to me that condition "HRMSLDate"="HCMLHDate" (comparing two different constants) should have returned no records, rather than whole table.
0
 

Author Closing Comment

by:gtmathewDallas
ID: 39277400
Because I find it soon after I posting the question and before somebody answering it
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question