Perform searches in Access using, in the Where conditions, date type fields. Useful features.
Searches with dates in Access
We recently received a question from a friend who wanted to perform searches in Access using date type fields in the Where conditions. After several attempts we have rescued a couple of notes that may be interesting to publish. I’m sure these will help others who have to work with dates in Access queries.
The query was the following:
I have a table with several fields. Two of them are dates, which correspond to an interval. One of them is the start date of the interval (datefrom) and another is the end date of the interval (dateto).
I would like to know how you can make a SQL query in Access to get the records whose date range contains today.
That is, the date from is less than today and the date to is greater than today.
We were first doing a couple of unsuccessful tests, comparing dates with arithmetic operators. In some cases we got the expected response, but the statements didn’t always work and we had problems executing them from Access or from the web server, because they didn’t return the same results.
DateDiff() function
In the end, the answer we proposed was to use the DateDiff function, which is used to obtain the difference between two dates. For example:
DateDiff(“y”, #06/10/2004#, Now())
It tells us the days that have passed since October 6, 2004.
We can use it as a condition in a where of an SQL statement. For example, for a statement like this:
DateDiff(“y”,A,B)
- If they are equal, the function will return zero.
- If A is a date before B, then the function will return a number of days that will be greater than zero.
- If A is a date after B, then it will return a number of days that will be less than zero.
We have to compare today with the dates from and to. Today has to be greater than from and less than to. We are left with this sentence as a result.
SELECT * FROM flights WHERE
DateDiff(‘y’,datesince,now())>=0
and DateDiff(‘y’,dateto,name())
Note: You have to be careful with the language of the dates, because in Spanish they are written differently than in English. Access tries to interpret the date correctly, for example, if we enter 02/26/04 it will think that we are working on dates in English and if we enter 26/02/04 it will think that we are writing the dates in Spanish. The problem is with a date like 02/02/04 that its value will depend on how Access is configured, in Spanish or English.
DatePart function
It is used to extract part of a date. It receives two parameters, the first indicates the part to be obtained by means of a string. The other parameter is the date you want to work with.
DatePart(“m”,date)
In this case, it is indicating that you want to obtain the month of the year. Another possible value for the first parameter is, for example “yyyy”, which is used to get the four-digit year. An example of an SQL statement that uses this function can be the following:
SELECT DatePart(“yyyy”,validfrom) FROM flights
DateAdd function
This last function that we are going to see in this article is used to add something like days, months or years to the date. To do this, the function receives three parameters, the first corresponds to a string to indicate the units of what we want to add, for example, days, months or years. The second parameter is the number of days, months, or years to add, and the third parameter is the date to add those values to. We see an example of its syntax:
DateAdd(“yyyy”,10,validfrom)
In this example the DateAdd function would return a date ten years after the validfrom. Other values for the first parameter string are “d” to add days, or “m” to add months.
An example of how this function works in an SQL statement is the following:
SELECT DateAdd(“yyyy”,10,validfrom) FROM flights
Facebook Comments
Disqus