DateTime queries

[ennrike]

Hi,

I have a table with a column date time (it has day, month, year, hours and seconds).

I have two questions:

1: I would like to make a query of entries looking for dates, but I am only interested to compare the DAY, MONTH and YEAR only (ignore the seconds, hour and minute)

Lets say that I have in the same table a integer column (called "hours").

2: What would be faster (for the computer)?: To get only one table and pool the table to see what rows has the column "hours" bigger than 5hrs and other rows with hrs bigger than 12hrs?
or
it would be faster to perform two separated queries, getting the count of rows bigger thant 5hrs (one query) and other query to get bigger than 12hrs (second query)

Thanks,

 

[MarkK]

1) Check out the DateValue() function.
2) I don't understand this "To get only one table and pool the table," but unless you have millions of records I expect that either way the query will execute in milliseconds. Are you concerned about your users waiting around, or is the question academic in nature?

 

[ennrike]

Thank you for the answer,
Can you give me an example using DateValue, currently I am using:

SELECT * FROM MyTable WHERE Date > MyDate1 AND Date <= MyDate2
MyDate2 = MyDate1.AddDay(1)

I am using an interval instead of using "=", I would think that an "=" would be faster than comparing intervals.

For me speed mathers because the access file is in a network folder, and depending on the type of command sometimes it takes more time.

 

[MarkK]

What's ...

MyDate1.AddDay(1)

An example of using DateValue is...

SELECT DateValue(MyDateTime) As DateOnly FROM Table ...

 

[ennrike]

Sorry, I am using C#, by "MyDate1.AddDay(1)" I mean that I am adding one day to the MyDate1, so that, If I have MyDate1 = "01/02/2011.15:20:12" by adding one day would be MyDate2 = "02/02/2011.15:20:12"

By the way, how can I mark as a thread as answered, can I do that? or the moderators do.

 

[Brianwarnock]

If date2 =date1+1day then
WHERE Date > MyDate1 AND Date <= MyDate2
might aswell be
WHERE Date = MyDate2

with the addition of Datevalue as shown by Lagbolt

Brian

 

[ennrike]

I would like to use:
WHERE MyDate1 = MyDate2

But sometimes this dates will have different hours, minutes and seconds, so that, I would like to make the comparison only with the Day, Month and Year only.

can I do?:
WHERE DateOnly(MyDate1) = DateOnly(MyDate2)

 

[Brianwarnock]

Lagbolt has explained, use Datevalue

Brian

 

[raskew]

If you're still having problems, here are examples, from the debug window, that
might simplify the situation:

x = now()
? x
2/1/2011 7:52:01 PM

...as it's stored by Access
? cdbl(x)
40575.8277893519

...using DateValue() function
? DateValue(x)
2/1/2011

HTH - Bob