Correct Date Format   -  YYYY-MM-DD

SQL

To work with date fields in SQL, we'll use the Date and Time functions that Access supplies. Note that those functions are available in just about every environment that supports SQL.

The main functions: NOW( ) and DATE( ) return the current date. The difference between the two is that NOW( ) returns date and time, at this moment, and DATE( ) returns only the current date.

In Access, a date or time constant must be identified with # ... #, as in:
... WHERE p_startdate = #2001-01-01#;

 

Date formats If you intend to do e-commerce in the global village, you have to understand that different folks have different ways of doing things. For example, if you are American and you tell your French girlfriend, the love of your life, that you'll meet her under the Eiffel tower on 01/02/03, there is a good chance that you'll never see her again. To you it is obvious that you specified the date as January 2nd, 2003. In France, as in other French areas, like Quebec, the date is understood to be the 1st of February, 2003. In your case, it may work out. If you straighten out the misunderstanding in time, you go back a month later and she's waiting for you. Good luck! To avoid problems, get used to using the ANSI international standard date format: yyyy-mm-dd, as: 2003-01-02. Note the use of the 4-digit year. Remember all that anguish we went through in 1999 with the 2-digit 00 year? We don't want that to happen again. Also, note that the separator is the dash character - , and not the slash /. To set the date format, go through the Windows Control Panel, Regional settings. Since SQL and Access get their formatting from Windows, the format will be selected automatically.

DateDiff('interval', #date1#, #date2#) returns the time difference between date1 and date2, expressed in interval units which could be: days, months, years, weeks or hours.

The interval is specified as: 'd' for days, 'w' for weeks, 'm' for months and 'yyyy' for years.

For example:
 

Datediff('d', #2001-01-01#, now()) returns the number of days between January 1st and today.

Datediff('m', p_StartDate, p_EndDate) returns the length of the project, in months.

If the result displays too many numbers after the decimal, use the ROUND(number, digits) function to display the number rounded to 'digits' positions after the decimal:
ROUND(Datediff('m', p_StartDate, p_EndDate), 2).
 

To calculate the exact age, use the following formula:

INT(Datediff('d', e_BirthDate, now())/365.25)

Calculate the number of days and divide by the exact number of days in a year, which, as you know, is 365.25 and not 365. That takes leap years into account.
The INT( ) function truncates the result so that 25.9 becomes 25, for example; the employee is 25 years old until the day she turns 26; after the age of 5, you rarely hear people say that they are 25 and a half years old.

When working with age, remember that you can often use Date-of-birth directly, without doing the age calculation. Don't forget that the smallest date refers to the oldest person.

VB
Function CalcAge(datEmpDateOfBirth As Variant) As Integer
    CalcAge = Int(DateDiff("y", CDate(datEmpDateOfBirth), Date) / 365.25)
End Function