D.4.0- Date and Time Functions and Statements

Created by Brendan Doss.
Last Updated by Sarah Welna.

Public Categorized as Appendix D.

Not yet tagged

D.4.1- Unsupported Date Functions and Statements >>

Date and Time Functions and Statements

There are a number of ways to display and represent dates and times. This includes date literals, which are valid date expression, enclosed in number signs (#). You need to be careful when using date literals because VBScript only lets you use the US-English date format, mm/dd/yyyy. This is true even if a different locale is being used on the machine. This might lead to problems when trying to use date literals in other formats, because in most cases the date will be accepted although converted to a different date. #10/12/1997# will be interpreted as October 12, 1997, but you might in fact want December 10, 1997, because your locale settings interprets dates as dd/mm/yyyy. Date literals only accept the forward slash (/) as the date separator.

The data range for a date is January 1, 100 to December 31, 9999, both inclusive. Internally, dates are stored as part of real numbers or to be more specific as a Variant subtype Double (5). The digits to the left of the decimal separator represent the date and the digits to the right of the decimal separator represent the time. Negative numbers are used internally for representing dates prior to December 30, 1899.

Below is a list of functions used for converting and formatting dates and times.

CDate

Returns an expression converted to Variant subtype Date (7).

Syntax

CDate(date)

date is any valid date expression.

Note

CDate is internationally aware, which means that the return value is based on the locale settings on the machine. Dates and times will be formatted with the appropriate time and date separators, and for dates the correct order of year, month and day are applied. Date and time literals are recognized.

Example

Dim dtmValue

dtmValue = CDate( #12/10/1997#)

dtmValue now holds the value "10-12-97", if your locale settings use the dash (–) as the date separator and the short date format is dd/mm/yy.

See Also

IsDate

Date	Returns a Variant subtype Date (7) indicating the current system date.
Syntax	Date
Example	MsgBox Date Assuming that today is July 29 1999, the MsgBox now displays 29-07-99, if your locale settings use the dash (–) as the date separator and the short date format is dd/mm/yy.
See Also	Now and Time

DateAdd

Adds or subtracts a time interval to a specified date and returns the new date.

Syntax

DateAdd(interval, number, date)

interval can have these values:

d Day

h Hour

m Month

n Minute

q Quarter

s Second

	w Weekday ww Week of year y Day of year yyyy Year number is a numeric expression that must be positive if you want to add or negative if you want to subtract. number is rounded to the nearest whole number if it's not a Long value. date must be a Variant or date literal to which interval is added.
Note	DateAdd is internationally aware, which means that the return value is based on the locale settings on the machine. Dates and times will be formatted with the appropriate time and date separators and for dates the correct order of year, month and day are applied. An error occurs if the date returned precedes the year 100.
Example	MsgBox DateAdd("m", 3, "1-Jan-99") This will add 3 months to January 1, 1999 and the MsgBox now displays 01-04-99, if your locale settings use the dash (–) as the date separator and the short date format is dd/mm/yy.
See Also	DateDiff, DatePart

DateDiff

Returns the interval between two dates.

Syntax

DateDiff(interval, date1, date2, [firstdayofweek], [firstweekofyear])

interval can have these values:

d Day

h Hour

m Month

n Minute

q Quarter

s Second

w Weekday

ww Week of year

y Day of year

yyyy Year

date1 and date2 are date expressions.

firstdayofweek (Optional) specifies the first day of the week. Use one of the following constants:

	vbUseSystemDayOfWeek 0 (National Language Support (NLS) API setting. NLS functions help Win32-based applications support the differing language- and location-specific needs of users around the world.) vbSunday 1 (default) vbMonday 2 vbTuesday 3 vbWednesday 4 vbThursday 5 vbFriday 6 vbSaturday 7 firstweekofyear (Optional) specifies the first week of the year. Use one of the following constants: vbUseSystem 0 (Use NLS API setting) vbFirstJan1 1 (default) Week in which January 1 occurs. vbFirstFourDays 2 First week in the new year with at least four days. vbFirstFullWeek 3 First full week of the new year.
Note	A negative number is returned if date1 is later in time than date2.
Example	MsgBox DateDiff("yyyy", #11-22-1967#, Now) This will calculate the number of years between 11/22/1967 and now. In 1999, the MsgBox will display 32.
See Also	DateAdd, DatePart

DatePart

Returns a specified part of a date.

Syntax

DatePart(interval, date, [firstdayofweek], [firstweekofyear])

interval can have these values:

d Day

h Hour

m Month

n Minute

q Quarter

s Second

w Weekday

ww Week of year

y Day of year

yyyy Year

date is a date expression.

firstdayofweek (Optional) specifies the first day of the week. Use one of the following constants:

vbUseSystemDayOfWeek 0 (NLS API setting)

vbSunday 1 (default)

vbMonday 2

vbTuesday 3

vbWednesday 4

vbThursday 5

vbFriday 6

vbSaturday 7

firstweekofyear (Optional) specifies the first week of the year. Use one of the following constants:

vbUseSystem 0 (Use NLS API setting)

vbFirstJan1 1 (default) Week in which January 1
occurs.

vbFirstFourDays 2 First week in the new year with at
least four days.

vbFirstFullWeek 3 First full week of the new year.

Example

MsgBox DatePart("ww", Now, vbMonday, vbFirstFourDays)

This will extract the week number from the current system date. On July 29, 1999 the MsgBox will display 30.

See Also

DateAdd, DateDiff

DateSerial

Returns a Variant subtype Date (7) for the specified year, month and day.

Syntax

DateSerial(year, month, day)

year is an expression that evaluates to a number between 0 and 9999. Values between 0 and 99, both inclusive, are interpreted as the years 1900 – 1999.

month is an expression that must evaluate to a number between 1 and 12.

day is an expression that must evaluate to a number between 1 and 31.

Note

If an argument is outside the acceptable range for that argument, it increments the next larger unit. Specifying 13 as the month will automatically increment year by one and subtract 12 from month leaving a value of 1. The same is true for negative values and a value of 0. However, instead of incrementing, the next larger unit is decremented.

An error occurs if any of the arguments is outside the Variant subtype Integer range, which is -32768 – +32767. The same is true if the result
is later than December 31, 9999. If you specify the year as 0, and the month and day as 0 or a negative value, the function wrongly assumes that the year is 100 and decrements this value.

So DateSerial(0, 0, 0) returns 11/30/99.

Example

MsgBox DateSerial( 1999, 07, 29)

The MsgBox will display 29-07-99, if your locale settings use the dash (–) as the date separator and the short date format is dd/mm/yy.

See Also

Date, DateValue, Day, Month, Now, TimeSerial, TimeValue, Weekday and Year

DateValue

Returns a Variant subtype Date (7).

Syntax

DateValue(date)

date is an expression representing a date, a time, or both, in the range January 1, 100 – December 31, 9999.

Note

Time information in date is not returned, but invalid time information
will result in a runtime error. DateValue is internationally aware and
uses the locale settings on the machine, when recognizing the order of a date with only numbers and separators. If the year is omitted from date,
it is obtained from the current system date.

Example

DateValue("07/29/1999")
DateValue("July 29, 1999")
DateValue("Jul 29, 1999")
DateValue("Jul 29")

All of the above will return the same valid date of 07/29/99.

See Also

Date, DateSerial, Day, Month, Now, TimeSerial, TimeValue, Weekday and Year

Day	Returns a number between 1 and 31 representing the day of the month.
Syntax	Day(date) date is any valid date expression.
Note	A runtime error occurs if date is not a valid date expression. Null will be returned if date contains Null.
Example	MsgBox Day("July 29, 1999") The MsgBox will display 29.
See Also	Date, Hour, Minute, Month, Now, Second, Weekday and Year

FormatDateTime

See under String functions

Hour

Returns an integer between 0 and 23, representing the hour of the day.

Syntax

Hour(time)

time is any valid time expression.

Note

A runtime error occurs if time is not a valid time expression. Null will be returned if time contains Null.

Example

MsgBox Hour("12:05:12")

The MsgBox will display 12.

See Also

Date, Day, Minute, Month, Now, Second, Weekday and Year

IsDate

Returns a Variant subtype Boolean (11) indicating whether an
expression can be converted to a valid date.

Syntax

IsDate(expression)

expression is any expression you want to evaluate as a date or time.

Example

MsgBox IsDate(Now) ' true
MsgBox IsDate("") ' false
MsgBox IsDate(#7/29/1999#) ' true

See Also

CDate, IsArray, IsEmpty, IsNull, IsNumeric, IsObject and VarType

Minute

Returns a number between 0 and 59, both inclusive, indicating the
minute of the hour.

Syntax

Minute(time)

time is any valid time expression.

Note

A runtime error occurs if time is not a valid time expression. Null will
be returned if time contains Null.

Example

MsgBox Minute("12:45")

The MsgBox will display 45.

See Also

Date, Day, Hour, Month, Now, Second, Weekday and Year

Month	Returns a number between 1 and 12, both inclusive, indicating the month of the year.
Syntax	Month(date) date is any valid date expression.
Note	A runtime error occurs if date is not a valid date expression. Null will be returned if date contains Null.
Example	MsgBox Month(#7/29/1999#) The MsgBox will display 7.
See Also	Date, Day, Hour, Minute, Now, Second, Weekday and Year

MonthName

Returns a Variant subtype String (8) for the specified month.

Syntax

MonthName(month, [abbreviate])

month is a number between 1 and 12 for each month of the year beginning with January.

abbreviate (Optional) is a boolean value indicating if the month name should be abbreviated or spelled out (default)

Note

A runtime error occurs if month is outside the valid range (1-12). MonthName is internationally aware, which means that the returned strings are localized into the language specified as part of your locale settings.

Example

MsgBox MonthName(2) ' February
MsgBox MonthName(2, true) ' Feb

See Also

WeekdayName

Now	Returns the system's current date and time.
Syntax	Now
Example	Dim dtmValue dtmValue = Now dtmValue now holds the current system date and time.
See Also	Date, Day, Hour, Month, Minute, Second, Weekday and Year

Second

Returns a Variant subtype Date (7) indicating the number of seconds (0-59) in the specified time.

Syntax

Second(time)

time is any valid time expression.

Note

A runtime error occurs if time is not a valid time expression. Null will
be returned if time contains Null.

Example

MsgBox Second("12:45:56")

The MsgBox will display 56.

See Also

Date, Day, Hour, Minute, Month, Now, Weekday and Year

Time	Returns a Variant subtype Date (7) indicating the current system time.
Syntax	Time
Example	Dim dtmValue dtmValue = Time dtmValue now holds the current system time.
See Also	Date, Now

Timer

Returns a Variant subtype Single (5) indicating the number of seconds that have elapsed since midnight. This means that it is "reset" every 24 hours.

Syntax

Timer

Example

Dim dtmStart, dtmStop

dtmStart = Timer
' Do processing here
dtmStop = Timer
' Display how many
' seconds the operation
' took
MsgBox dtmStop - dtmStart

TimeSerial

Returns a Variant subtype Date (7) for the specified hour, minute and second.

Syntax

TimeSerial(hour, minute, second)

hour is an expression that evaluates to a number between 0 and 23.

minute is an expression that must evaluate to a number between 0 and 59.

second is an expression that must evaluate to a number between 0 and 59.

Note

If an argument is outside the acceptable range for that argument, it increments the next larger unit. Specifying 61 as minute will
automatically increment hour by one and subtract 60 from minute
leaving a value of 1. The same is true for negative values and a value
of 0. However, instead of incrementing, the next larger unit is decremented.

An error occurs if any of the arguments is outside the Variant subtype Integer range, which is -32768 – +32767.

Example

MsgBox TimeSerial(23, 07, 29)

The MsgBox will display 23:07:29.

See Also

Date, DateSerial, DateValue, Day, Month, Now, TimeValue, Weekday and Year

TimeValue

Returns a Variant subtype Date (7) containing the time.

Syntax

TimeValue(time)

time is an expression in the range 0:00:00 – 23:59:59.

Note

Date information in time is not returned, but invalid date information
will result in a runtime error. Null is returned if time contains Null.
You can use both 24 and 12-hour representations for the time argument.

Example

TimeValue("23:59")
TimeValue("11:59 PM")

Both will return the same valid time.

See Also

Date, DateSerial, DateValue, Day, Month, Now, TimeSerial, Weekday and Year

Weekday

Returns a number indicating the day of the week.

Syntax

Weekday(date, [firstdayofweek])

date is any valid date expression.

firstdayofweek (Optional) specifies the first day of the week. Use one of the following constants:

vbUseSystemDayOfWeek 0 (Use NLS API setting)

vbSunday 1 (Default)

vbMonday 2

vbTuesday 3

vbWednesday 4

vbThursday 5

vbFriday 6

vbSaturday 7

Note

Null is returned if date contains Null. A runtime occurs if date
is invalid. Possible return values are:

vbSunday 1

vbMonday 2

vbTuesday 3

vbWednesday 4

vbThursday 5

vbFriday 6

vbSaturday 7

Example

Weekday(#July 29, 1999#)

Returns 5 for Thursday.

See Also

Date, Day, Month, Now and Year

WeekdayName

Returns a Variant subtype String (8) for the specified weekday.

Syntax

WeekdayName(weekday, [abbreviate], [firstdayofweek])

weekday is a number between 1 and 7 for each day of the week. This value depends on the firstdayofweek setting.

abbreviate (Optional) is a boolean value indicating if the weekday name should be abbreviated or spelled out
(default)

firstdayofweek (Optional) is a numeric value indicating the first day of the week. Use one of the following constants: