• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

Comparing two dates - Access 2010

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
gtmathewDallas
Asked:
gtmathewDallas
  • 3
  • 2
  • 2
  • +1
1 Solution
 
gtmathewDallasAuthor Commented:
Thanks I got it, I used DateValue with the fields, Thanks
0
 
BitsqueezerCommented:
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
 
PortletPaulCommented:
[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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
BitsqueezerCommented:
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
 
PortletPaulCommented:
>>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
 
BitsqueezerCommented:
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
 
Vadim RappCommented:
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
 
gtmathewDallasAuthor Commented:
Because I find it soon after I posting the question and before somebody answering it
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now