CODEWITHSUNDEEP
sqlms-access
bnil
bnil

Reputation: 1541

Giving the wrong records when used datetime parameter in MS Access Query

I am working MS-Access 2007 DB .

I am trying to write the query for the Datetime, I want to get records between 14 December and 16 December so I write the bellow query. 

SELECT * FROM Expense WHERE CreatedDate > #14-Dec-15# and CreatedDate < #16-Dec-15#

( I have to use the two dates for the query.)

But It returning the records having CreatedDate is 14 December... Whats wrong with the query ?

enter image description here

Upvotes: 2

Views: 97

Answers (5)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

You must inlcude the time in your thinking:

EDIT: I wrote this with the misunderstanding, that you wanted to include data rows from 14th to 16th of Dec (three full days).

If you'd write <#17-Dec-15# it would be the full 16th. Or you'd have to write <=#16-Dec-15 23:59:59#.

A DateTime on the 16th of December with a TimePart of let's say 12:30 is bigger than #16-Dec-15#...

Just some backgorund: In Ms-Access a DateTime is stored as a day's number and a fraction part for the time. 0.5 is midday, 0.25 is 6 in the morning...

Comparing DateTime values means to compare Double-values in reality.

Upvotes: 1

HansUp
HansUp

Reputation: 97101

It seems you want only records from Dec 15th regardless of the time of day stored in CreatedDate. If so, this query should give you what you want with excellent performance assuming an index on CreatedDate ...

SELECT *
FROM Expense
WHERE CreatedDate >= #2015-12-15# and CreatedDate < #2015-12-16#;

Beware of applying functions to your target field in the WHERE criterion ... such as CDATE(INT(CreatedDate)). Although logically correct, it would force a full table scan. That might not be a problem if your Expense table contains only a few rows. But for a huge table, you really should try to avoid a full table scan.

Upvotes: 1

bnil
bnil

Reputation: 1541

Thanks A Lot guys for your help...

I finally ended with the solution given by Darren Bartrup-Cook and Gustav ....

My previous query was.... 

SELECT * FROM Expense WHERE CreatedDate > #14-Dec-15# and CreatedDate < #16-Dec-15#

And the New working query is...

SELECT * FROM Expense WHERE CDATE(INT(CreatedDate)) > #14-Dec-15# and CDATE(INT(CreatedDate)) < #16-Dec-15#

Upvotes: 0

Gustav
Gustav

Reputation: 55806

Just add one day to your end date and exclude this:

SELECT * FROM Expense WHERE CreatedDate >= #2015/12/14# AND CreatedDate < #2015/12/17#

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15140

As @vkp mentions in the comments, there is a time part to a date as well. If it is not defined it defaults to midnight (00:00:00). As 14-dec-2015 6:46:56 is after 14-dec-2015 00:00:00 it is included in the result set. You can use >= 15-dec-15 to get around this, as it will also include records from 15-dec-2015. Same goes for the end date.

Upvotes: 2

Related Questions