sql query to select week,month, days

please guys, i need an sql query to select data from an access database using vb6. the query will only provide current date and the filed been compaired is curr_date in the database.

for example

SELECT * 
  FROM TABLE 
 WHERE curr_date BETWEEN firstoflastmonth AND endof开发者_开发百科lasthmonth;

SELECT * 
  FROM TABLE 
 WHERE curr_date BETWEEN firstoflastweek AND endoflasthweek;

SELECT * 
  FROM TABLE 
 WHERE curr_date BETWEEN firstofthismonth AND endofthismonth;

SELECT * 
  FROM TABLE 
 WHERE curr_date BETWEEN firstofthsiweek AND tilldate;

The problem i have figuring ot is how to specify the two dates in comparison

---

The Date() function will return the current timestamp. Use the ms-access date functions to work backwards/forwards from there, for example:

DateAdd("m",2,Date())

Adds two months to the current date. check this reference out

---

Is it not easiest to just check wich month/week you want it to be in then?

From you examples:

SELECT *    FROM TABLE   
WHERE curr_date BETWEEN firstoflastmonth AND endoflasthmonth;  

This would become (with the month function used)

SELECT *    FROM TABLE   
WHERE month(curr_date) = month(dateadd("m",-1,Date()));

This compares the month of your curr_date to the month of Date (the current date) minus one month, so the last month. This can also be done using datediff:

SELECT *    FROM TABLE   
WHERE DateDiff ( "m", curr_date, date()) = 1
AND curr_date < date();

The last line is added to make sure that dates one month later are not selected.

Same thing can be applied to the other queries using the date function found here

Links used to the reference sheet provided by Richard Calahan

---

See:

Why should I consider using an auxiliary calendar table?

The article's code is specifically for SQL Server but the techniques are portable to most SQL platforms (and even MS Access :) The idea is that SQL is a declarative language and will favour a data-driven set-based solution over a calucate-procedurally-on-the-fly approach.

You'd have one row for each and every day required by your enterprise, covering a few decades will not amount to much (thousands of rows). Each of those days will have values for firstoflastmonth, firstoflastweek, etc (note sure what tilldate means, though); these values are generated once e.g. using a spreadsheet. You can then JOIN to this table using NOW(), Access's (ACE's, Jet's, whatever's) implementation of Standard SQL's CURRENT_TIMESTAMP.

Further note that because Access uses double float values to model all temporal data, BETWEENs euality operation is usally unsuitable. So rather than this:

SELECT * 
  FROM TABLE 
 WHERE curr_date BETWEEN firstoflastmonth AND endoflasthmonth;

...instead use this

SELECT * 
  FROM TABLE 
 WHERE (
        curr_date >= firstoflastmonth 
        AND curr_date < firstofthishmonth
       );