Saturday, June 3, 2017

Choose the right date function

Choose the right date function

Access provides built-in functions you can use in expressions to format, create, parse, and calculate dates and times. Pick a table that matches what you want to do.

I want to…

Display a date or time in a specific format

Split a date or time into parts

Turn numbers and text into dates and times

Add or subtract dates

Insert today's date or the current time

Display a date or time in a specific format

Display dates in the format you want. The Result column assumes that the [StartDate] field contains the Date/Time value of January 11, 2012 17:30:15.

Function

Use

Example

Result

Format*

All-purpose format function used to create custom Date/Time formats.

Format([StartDate],"yyyy-mm-dd")

2012-01-11

FormatDateTime*

Applies one of 5 built-in Date/Time formats to a value.

FormatDateTime([StartDate],vbLongDate)

Wednesday, January 11, 2012

MonthName*

Converts a number to a month name. Returns an error if you supply a number outside the range of 1-12.

MonthName(12)

December

WeekdayName*

Converts a number to a weekday name. Returns an error if you supply a number outside the range of 1-7.

WeekdayName(2)

Monday

* Not available in Access apps.

Learn more about how to format dates in desktop databases and Access apps.

Top of Page

Split a date or time into parts

Sometimes you just need to know one part of a Date/Time value, such as the year or the minute. These functions all return integer values that correspond to the interval you're looking for. The Result column assumes that [StartDate] contains the Date/Time value of Jan 11, 2012 17:30:15.

Function

Use

Example

Result

Year

Returns the year portion of the Date/Time value.

Year([StartDate])

2012

Month

Returns the month portion of the Date/Time value (1 to 12).

Month([StartDate])

1

Day

Returns the day portion of the Date/Time value (1 to 31).

Day([StartDate])

11

Weekday*

Returns the numeric day of the week (1 to 7). By default, Sunday is considered the first day of the week, but you can specify a different day as the first day.

Weekday([StartDate])

4

Hour*

Returns the hour portion of the Date/Time value (0 to 23).

Hour([StartDate])

17

Minute*

Returns the minutes portion of the Date/Time value (0 to 59).

Minute([StartDate])

30

Second*

Returns the seconds portion of the Date/Time value (0 to 59).

Second([StartDate])

15

DatePart

A configurable version of the functions above, where you can specify which part of the Date/Time value you want.

DatePart("yyyy",[StartDate])

2012

* Not available in Access apps.

Top of Page

Turn numbers and text into dates and times

Access stores dates as double-precision floating point numbers. For example, January 11, 2012 5:30 PM is stored as 40919.72917. The integer portion, to the left of the decimal point, represents the date. The fractional portion, to the right of the decimal point, represents the time. These functions help you create this floating point number from other values, such as integers or text.

Function

Use

Example

Result (How Access stores it)

DateSerial

Note:  In an Access app, use the DateFromParts or DateWithTimeFromParts functions.

Builds a date value from integers you supply for the year, month, and day.

DateSerial(2012,1,11)

40919.0

CDate*

Converts text to a Date/Time value. Handles both the Date and Time portion of the number.

Tip:  Use the BooleanIsDate function to determine if a text string can be converted to a Date/Time value. For example, IsDate("1/11/2012") returns True.

CDate("1/11/2012 17:30")

40919.72917

DateValue*

Converts text to a Date/Time value, but doesn't convert the time portion.

DateValue("1/11/2012 17:30")

40919.0

TimeValue*

Converts text to a Date/Time value, but doesn't convert any date information.

TimeValue("1/11/2012 17:30")

0.72917

TimeSerial

Note:  In an Access app, use the TimeFromParts or DateWithTimeFromParts functions.

Builds a time value from integers you supply for the hour, minute, and second.

TimeSerial(17,30,15)

0.72934

* Not available in Access apps.

Top of Page

Add or subtract dates

These functions let you add or subtract intervals to dates, as well as calculate the difference between two Date/Time values.

Function

Use

Example

Result

DateAdd

Adds a specific interval (such as 2 months or 3 hours) to a Date/Time value. To subtract, use a negative number as the second argument.

DateAdd("m", 1, "1/11/2012")

2/11/2012

DateDiff

Determines the number of time intervals (such as days or minutes) between two Date/Time values.

Note:  Keep in mind you can also just subtract one date value from another to get the number of days between them, for example, #2/15/2012# - #2/1/2012# = 14.

DateDiff("d","1/11/2012","4/15/2012")

95

See how to calculate the number of days between two dates.

Top of Page

Insert today's date or the current time

Use these functions to determine the current date or time. The Result column assumes that the current system date/time is Jan 11, 2012 17:30:15.

Function

Use

Example

Result

Now

Returns the current system date and time.

Now()

1/11/2012 5:30:15 PM

Date

Note:  In an Access app, use the Today function.

Returns the current system date.

Date()

1/11/2012

Time*

Returns the current system time.

Time()

5:30:15 PM

Timer*

Returns the number of seconds elapsed since midnight.

Timer()

63015

* Not available in Access apps.

Top of Page

No comments:

Post a Comment