quering date AND Month

J

JAM

Member
Joined
Mar 9, 2003
Messages
24
Hi every one
I have 2 Problems
1. I store dates in the d/M/yyyy format, using Access. But when quering from vb.net it deals with it using this format M/d/yyyy.
Any Idea?

2. I need to query data for a specific month. lets say that I need to get all invoices for March 2003. I couldnt figure out how to deal with th month and the year only and disregard the day?

Please help.
 
1) You should ALWAYS store dates in yyyy/mm/dd format, regardless. Regardless of how the database actually displays it to you when you view the data, undear the hood this is how its treated and delt with (if its not then theres something seriously wrong). When retrieving dates, put them into a DateTime object and use them from there.

2) Im not sure if Access has this, but in SQL Server theres a function called MONTH() and another called YEAR(). You could do something like this;

SELECT date, some_data FROM table WHERE MONTH(date) = 3 AND YEAR(date) = 2003

There is a seriously problem with this however; it does not use indexes when searching for the correct month and year. Instead, it will traverse the entire table. This is bad.

A better query and far more efficient (as itd take advantage of indexes in place) would be to do something like this;

SELECT date, some_data FROM table WHERE date >= 3/1/2003 AND date < 4/1/2003

If all youre doing is keeping track of the month and year in your program, and not using the days, then you can gain even further query efficiency by using some database integrity and storing all dates as the 1st of the month. Then you can do something like this;

SELECT date, some_data FROM table WHERE date = 3/1/2003

Keep in mind this is coming from a SQL Server perspective, although you should still be able to accomplish this in Access.
 
Thank you so much
following your recomendation #1 is solved now.
I will work on #2 now.
 
Back
Top