(3.16) The use of all dates and times in SQL statements

DATEADD function
Description: Returns the date on which the specified time interval has been added.
Syntax DateAdd (interval, number, date)
Interval: Must be selected. A string expression that represents the time interval to add. See the Settings section for values.
Number: Must be selected. A numeric expression that represents the number of time intervals to add. A numeric expression can be a positive number (get a future date) or negative (get a date in the past).
Date: Must be selected. Variant or to add a interval of the text that represents the date.
The interval parameter can have the following values:
YYYY (year), Q (Quarter), M (month), Y (Days of the year), D (Day), W (Days of the Week), WW (weeks), H (Hours), N (minutes), s (seconds)
Description: The DATEADD function can be used to add or subtract a specified time interval from a date. For example, you can use DATEADD from the day after 30 days or 45 minutes from now. To add a time interval in days to date, you can use the day of the Year (Y), day (d), or Day of the week ("W").
The DATEADD function does not return an invalid date. The following example adds January 31, 95 plus one months:
Newdate = DateAdd ("M", 1, "31-jan-95")
In this example, DATEADD returns February 28, 95, not February 31, 95. If date is January 31, 96, it returns February 29, 96, because 1996 is a leap year.
If the calculated date is before 100 A.D., an error is generated.
If number is not a Long value, it is rounded to the nearest integer before the calculation.

DateDiff function
Description: Returns a time interval between two dates.
Grammar DateDiff (interval, date1, date2 [, firstdayofweek[, firstweekofyear>)
Interval: Must be selected. A string expression that is used to calculate the time interval between Date1 and Date2. See the Settings section for values.
Date1, Date2: Required. The date expression. The two dates used for the calculation.
FirstDayOfWeek: Optional. Constant that specifies the first day of the week. If not specified, the default is Sunday. See the Settings section for values.
FirstWeekOfYear: Optional. A constant that specifies the first week of the year. If not specified, the default is the week in which January 1 is located. See the Settings section for values.
The interval parameter can have the following values:
YYYY (year), Q (Quarter), M (month), Y (Days of the year), D (Day), W (Days of the Week), WW (weeks), H (Hours), N (minutes), s (seconds)
The FirstDayOfWeek parameter can have the following values:
(The following are: Constant value description)
Vbusesystem 0 uses the regional language Support (NLS) API settings.
Vbsunday 1 Sunday (default)
Vbmonday 2 weeks A
Vbtuesday 3 Week II
Vbwednesday 4 week Three
Vbthursday 5 Thu
Vbfriday 6 Friday
Vbsaturday 7 Saturday
The FirstWeekOfYear parameter can have the following values:
(The following are: Constant value description)
Vbusesystem 0 uses the regional language Support (NLS) API settings.
VbFirstJan1 1 starts from the week of January 1 (default).
vbFirstFourDays 2 starts with the first week of at least four days in the new year.
Vbfirstfullweek 3 starts with the first full week in the new year.
Description The DateDiff function is used to determine the number of specified time intervals that exist between two dates. For example, you can use DateDiff to calculate the number of days that differ by two dates, or the day of the week between the last day of the year.
To calculate the number of days between Date1 and Date2, you can use the days of the year ("Y") or the "Day" ("D"). When interval is "Days of the Week" ("W"), DateDiff returns the number of weeks between two dates. If Date1 is Monday, DateDiff calculates the number of Monday before Date2. This result contains date2 and does not contain date1. If interval is "Week" ("WW"), the DateDiff function returns the number of weeks between two dates in the Calendar table. The function calculates the number of Sunday between Date1 and Date2. If Date2 is Sunday, DateDiff calculates date2, but even if Date1 is Sunday, date1 is not counted.
If Date1 is later than Date2, the DateDiff function returns a negative number.
The FirstDayOfWeek parameter has an effect on the calculations that use the "w" and "ww" interval symbols.
If Date1 or date2 is a date literal, the specified year becomes a fixed part of the date. However, if Date1 or date2 is included in quotation marks ("") and the year is omitted, the current year is inserted each time the Date1 or date2 expression is evaluated in the code. This allows you to write program code that applies to different years.
When the interval is "year" ("yyyy"), compare December 31 and the next year January 1, although actually only one day, DateDiff returns 1 indicates a difference of one year.

DatePart function
Description: Returns the specified part of the given date.
Grammar datepart (interval, date[, firstdayofweek[, firstweekofyear>)
DatePart: The syntax of a function has the following parameters:
Interval: Must be selected. A string expression that represents the interval of time to return. See the Settings section for values.
Date: Must be selected. The date expression to evaluate.
Firstdayof Week: Optional. Constant that specifies the first day of the week. If not specified, the default is Sunday. See the Settings section for values.
FirstWeekOfYear: Optional. A constant that specifies the first week of the year. If not specified, the default is the week in which January 1 is located. See the Settings section for values.
The interval parameter can have the following values:
YYYY (year), Q (Quarter), M (month), Y (Days of the year), D (Day), W (Days of the Week), WW (weeks), H (Hours), N (minutes), s (seconds)
The FirstDayOfWeek parameter can have the following values:
(The following are: Constant value description)
Vbusesystem 0 uses the regional language Support (NLS) API settings.
Vbsunday 1 Sunday (default)
Vbmonday 2 weeks A
Vbtuesday 3 Week II
Vbwednesday 4 week Three
Vbthursday 5 Thu
Vbfriday 6 Friday
Vbsaturday 7 Saturday
The FirstWeekOfYear parameter can have the following values:
(The following are: Constant value description)
Vbusesystem 0 uses the regional language Support (NLS) API settings.
VbFirstJan1 1 starts from the week of January 1 (default).
vbFirstFourDays 2 starts with the first week of at least four days in the new year.
Vbfirstfullweek 3 starts with the first full week (not straddling) in the new Year.
Description The DatePart function is used to calculate the date and return the specified time interval. For example, use DatePart to calculate the day of the week or the current time.
The FirstDayOfWeek parameter affects the calculation using the "W" and "ww" interval symbols.
If date is a day literal, the specified year becomes a fixed part of the date. However, if date is enclosed in quotation marks ("") and the year is omitted, the current year is inserted each time the date expression is evaluated in the code. This allows you to write program code that applies to different years.
DateSerial function
Description: Returns a Variant of the date subtype for the specified year, month, and day.
Grammar dateserial (year, month, day)
Year: A numeric or numeric expression from 100 to 9999.
Month: An arbitrary numeric expression.
Day: An arbitrary numeric expression.
Note: To specify a date, such as December 31, 1991, the range of values for each parameter in the DateSerial function should be acceptable, and the value for the day should be between 1 and 31, and the month value should be between 1 and 12. However, you can also specify a relative date for each parameter by using a numeric expression that represents the number of years, months, and days before or after a day.
The following example uses a numeric expression instead of an absolute date. Here, the DateSerial function returns the date of 10 years (1990-10) 2 months (8-2) and another day (1-1) before August 1, 1990: May 31, 1980.
DateSerial (1990-10, 8-2, 1-1)
For the year parameter, if the value range is from 0 to 99, it is interpreted as 1900-1999. For the year parameter outside of this range, a four-bit number is used to represent the years (for example, 1800).
When the value of any one parameter exceeds an acceptable range, it is properly rounded to the next larger unit of time. For example, if you specify 35 days, the number of days is interpreted as a month plus an extra number of days, and the extra days depend on their year and month. However, an error occurs if the value of the parameter exceeds the range of 32,768 to 32,767, or if the date specified by three parameters, whether directly or by an expression, exceeds the acceptable date range.

Datevalue function
Description: Returns a Variant of the Date subtype.
Syntax DateValue (date)
The date parameter should be a string expression that represents a date from January 1 to December 31, 9999 in the 100 year. However, date can also be any expression that represents a mix of dates, times, or DateTime in the above range.
Description: If the date parameter contains time information, Datevalue does not return the time information. However, if date contains invalid time information (such as "89:98"), an error occurs.
If date is a string containing only numbers separated by a valid date delimiter, DateValue will identify the order of the month, day, and year based on the short date format specified for the system. DateValue also identifies an explicit date that contains the month name, whether full or abbreviated. For example, in addition to being able to identify 12/30/1991 and 12/30/91, DateValue can also identify December 30, 1991, and Dec 30, 1991.
If the year part of date is omitted, Datevalue will use the current year in the computer system date.

Day function
Description: Returns an integer from 1 to 31, including 1 and 31, representing the day of the month.
Syntax Day (date)
The date parameter is any expression that can represent a date. If NULL is included in the date parameter, NULL is returned.

FormatDateTime function
Description: Returns an expression that has been formatted as a date or time.
Syntax: FormatDateTime (Date[,namedformat])
Date: Must be selected. The date expression to be formatted.
NamedFormat: Optional. Indicates the numeric value of the date/time format used, and if omitted, uses Vbgeneraldate.
The NamedFormat parameter can have the following values:
(The following are: Constant value description)
Vbgeneraldate 0 Displays the date and/or time. If there is a date part, the section is displayed as a short date format. If there is a time section, the section is displayed as a long-time format. If they all exist, all sections are displayed.
vbLongDate 1 Displays the date using the long date format specified in the computer's regional settings.
Vbshortdate 2 Displays the date using the short date format specified in the computer's regional settings.
Vblongtime 3 Displays the time using the time format specified in the computer's regional settings.
vbShortTime 4 Displays the time using the 24-hour format (hh:mm).

Hour function
Description: Returns an integer from 0 to 23 (including 0 and 23) that represents an hour of the day.
Syntax: Hour (Time)
The time parameter is any expression that can represent the times. If NULL is included in the time parameter, NULL is returned.

Minute function
Description: Returns an integer from 0 to 59 (including 0 and 59) that represents a minute in an hour.
Syntax: Minute (Time)
The time parameter is any expression that can represent the times. If the time parameter contains null, NULL is returned.

Month function
Description: Returns an integer from 1 to 12 (including 1 and 12) that represents the month of the year.
Syntax: Month (date)
The date parameter is any expression that can represent a date. If NULL is included in the date parameter, NULL is returned.

MonthName function
Description: Returns a String indicating the specified month.
Syntax: MonthName (month[, abbreviate])
Month: Must be selected. The numeric definition of the month. For example, January is 1, February is 2, and so on.
Abbreviate: Optional. A Boolean value that indicates whether the month name is abbreviated. If omitted, the default value is False, that is, the month name is not abbreviated.

Now function
Description: Returns the current date and time value based on the date and time set by the computer system.
Syntax: now

Second function
Description: Returns an integer from 0 to 59 (including 1 and 59) that represents a second in a minute.
Syntax: Second (Time)
The time parameter is any expression that can represent the times. If NULL is included in the time parameter, NULL is returned.

Time function
Description: Returns the date subtype Variant, indicating the current system time.
Syntax: Time

TimeSerial function
Description: Returns a Variant of the date subtype containing the time, minutes, and seconds of the timer.
Syntax: TimeSerial (hour, minute, second)
Hour: The value is a numeric or numeric expression from 0 (a.m.) to (p.m.).
minute: An arbitrary numeric expression.
Second: an arbitrary numeric expression.
Note: To specify a moment, such as the 11:59:59,timeserial parameter value should be within an acceptable range, that is, the hour should be between 0-23, minutes and seconds should be between 0-59. However, you can use numeric expressions to specify a relative time for each parameter, which represents the number of hours, minutes, or seconds before or after a certain time. The following example uses an expression instead of an absolute time. The TimeSerial function returns six hours (12-6) 15 minutes before noon (-15), or 5:45:00 a.m..
TimeSerial (12-6,-15, 0)
When the value of any parameter exceeds an acceptable range, it is correctly rounded to the next larger unit of time. For example, if you specify 75 minutes, the time is interpreted as an hour and 15 minutes. However, if any one of the parameter values exceeds the range of 32768 to 32767, an error is caused. An error can also result if the time that is specified directly with three parameters or the time calculated through an expression exceeds the acceptable date range.

TimeValue function
Description: Returns a Variant of the date subtype containing the time.
Syntax: TimeValue (Time)
The time parameter is typically a string expression (including 0:00:00 and 23:59:59) that represents the 0:00:00 (12:00:00 a.m.) to 23:59:59 (11:59:59 p.m.). However, time can also be an expression that represents any period in the range. If the time parameter contains null, NULL is returned.
Note: You can enter the time in a 12 or 24-hour clock format. For example, "2:24pm" and "14:24" are valid time parameters.
If the time parameter contains date information, the TimeValue function does not return date information. However, if the time parameter contains invalid date information, an error occurs.

Weekday function
Description: Returns an integer representing the day of the week.
Syntax: Weekday (date, [FirstDayOfWeek])
Date: An arbitrary expression that can represent a date. If NULL is included in the date parameter, NULL is returned.
FirstDayOfWeek: A constant that specifies the first day of the week. If omitted, Vbsunday is used by default.
The FirstDayOfWeek parameter has the following settings:
(The following are: Constant value description)
Vbusesystem 0 uses the regional language Support (NLS) API settings.
Vbsunday 1 Sunday
Vbmonday 2 weeks A
Vbtuesday 3 Week II
Vbwednesday 4 week Three
Vbthursday 5 Thu
Vbfriday 6 Friday
Vbsaturday 7 Saturday
The Weekday function returns the following values:
(The following are: Constant value description)
Vbsunday 1 Sunday
Vbmonday 2 weeks A
Vbtuesday 3 Week II
Vbwednesday 4 week Three
Vbthursday 5 Thu
Vbfriday 6 Friday
Vbsaturday 7 Saturday

WeekdayName function
Description: Returns a String that represents the day of the week specified.
Syntax: WeekdayName (Weekday, abbreviate, FirstDayOfWeek)
Weekday: Must be selected. A numeric definition for the day of the week. The numeric definitions for each day depend on the FirstDayOfWeek parameter setting.
Abbreviate: Optional. A Boolean value that indicates whether the name of the day of the week is abbreviated. If omitted, the default value is False, that is, the name of the day of the week is not abbreviated.
FirstDayOfWeek: Optional. Indicates the value of the first day of the week. See the Settings section for values.
The FirstDayOfWeek parameter has the following values:
(The following are: Constant value description)
Vbusesystem 0 uses the regional language Support (NLS) API settings.
Vbsunday 1 Sunday (default)
Vbmonday 2 weeks A
Vbtuesday 3 Week II
Vbwednesday 4 week Three
Vbthursday 5 Thu
Vbfriday 6 Friday
Vbsaturday 7 Saturday

Year function
Description: Returns an integer that represents a year.
Syntax: Year (date)
The date parameter is any parameter that can represent a date. If NULL is included in the date parameter, NULL is returned

(3.16) The use of all dates and times in SQL statements