Solved

Comparing two dates - Access 2010

Posted on 2013-06-17
8
316 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
  • 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 48

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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 48

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now