Solved

Comparing two dates - Access 2010

Posted on 2013-06-17
8
336 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

830 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