?
Solved

Comparing two dates - Access 2010

Posted on 2013-06-17
8
Medium Priority
?
362 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

801 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