Locked lesson.

Upgrade

About this lesson

Date/Time is an important, but problematic, data type. Recognize Date/Time functions and formats and how to enter them into fields.

Quick reference

Date/Time Data Type

Application Terminology

Current Date

To enter the current date, press   (Ctrl-;  )

Current Time

To enter the current time, press  (Ctrl-Shift-; or Ctrl-: )

Date and Time

To enter date and time in a field, enter date, space, time.

Date/Time

Date/Time is an important, but problematic, data type.  Depending on where you are, dates and times are displayed differently, according to your Windows Region Settings.

Internally, a Date/Time is stored as a number, which is why the number sign is used to delimit date/time values.  Date and Time both measure the same thing: the passing of time.

A Date/Time value shows several pieces of information:

  • Year
  • Month
  • Day
  • Hour
  • Minute
  • Second
  • am/pm

The earliest date Access can reference is January 1, 100, which is -657,434

The maximum date Access can store is 31 Dec 9999, which is 2,958,465.

The Time part of a Date/Time is stored as a fraction.  Noon is really just halfway through a day.

Date and Time both measure the same thing, just in different terms.

Be careful when you are comparing dates as Access may have a time as well, even though a Format keeps it from showing.

Date/Time Functions

Now() is a function to return the current date and time.

Date() is a function to return the current date.

To return the current time, there is no built-in function.  You can use this equation:

TimeValue( Now() )

To construct a date from numbers for year, month, and day, use:

DateSerial( «year», «month», «day» )

For example, this is 25 December, 2016:
DateSerial( 2016, 12, 25 )

To construct a time from numbers for hour, minute, and second, use:

TimeSerial( «hour», «minute», «second» )

For example, this is 4:30 pm (notice you can also use expressions for the arguments:
TimeSerial( 12+4, 30, 0 )

Date/Time Formats

To see the number that is actually being stored for a date/time, here is one of the formats you can use:

#,##0.0000

You can get lots of information from a date/time by formatting it.  For instance, you can format a date/time to show you month names, day names, week numbers, and quarters.

The following is from Help.

 Format Property - Date/Time Data Type ___________________________________________________________________________________

Predefined Formats

The following table shows the predefined Format property settings for the Date/Time data type.

 

Custom Formats

You can create custom date and time formats by using the following symbols.

Custom formats are displayed according to the settings specified in the regional settings of Windows. Custom formats inconsistent with the settings specified in the regional settings of Windows are ignored.

Note

If you want to add a comma or other separator to a custom format, enclose the separator in quotation marks as follows: mmm d", "yyyy.

Steps

Enter Date and Time into a field

  1. Type the Date
  2. Type a space
  3. Type the Time

Change Date and Time Format for Windows

  1. Press Windows-X  to go to the Control Panel
  2. Choose Control Panel from the shortcut menu
  3. Choose Region.
  4. Change settings on the Formats tab
Login to download
  • 00:05 This is Access 2013, Lesson 16.
  • 00:08 Hi, this is Crystal.
  • 00:10 Date/Time is an important data type.
  • 00:13 Date/Time is one of the most problematic data types because
  • 00:17 dates are written differently depending on where you are.
  • 00:20 Because it is stored as a double precision number,
  • 00:23 exact comparisons cannot be done reliably.
  • 00:28 Each date and time also contains a lot of other information ...
  • 00:32 Year, Month, Day, Hour, Minute, Second ...
  • 00:39 All of these measure time passing.
  • 00:42 Understanding how to use and take advantage
  • 00:46 of the Date/Time Data Type is essential to mastering Access.
  • 00:51 Open your lesson file and
  • 00:53 go to the Design View of MyDataTypes.
  • 00:56 Make fields for Dates and fields for Time.
  • 00:59 The first field is dtm. The Data Type is Date/Time.
  • 01:05 The Field Description is Date and Time,
  • 01:09 and, in the lower pane, the Default Value is Now().
  • 01:15 Now() is a function that returns the current date and time
  • 01:19 and gets its information from your computer clock
  • 01:22 in the lower right corner of your screen.
  • 01:25 In new records, the value in a field will start out
  • 01:29 with a specified default value and can be changed by the user.
  • 01:34 The next field is dt. The Data Type is Date/Time.
  • 01:39 The Field Description is Date, and the Default Value =Date().
  • 01:47 The Date Function returns the current date.
  • 01:50 Copy field we just created by clicking
  • 01:54 in the field selector box and pressing Ctrl-C
  • 01:57 or Copy from the shortcut menu, right-mouse click.
  • 02:02 Right-click in the next row and choose Paste.
  • 02:05 The copied field will be inserted
  • 02:08 and the field you clicked in will move down.
  • 02:11 In this case, there are no other fields.
  • 02:14 Call this field dtShort and set the Format to Short Date.
  • 02:20 The next Date/Time field will be called dtComma.
  • 02:25 Set the Format to #,###.0###
  • 02:34 Next will be tm. The Data Type is Date/Time.
  • 02:39 Description is Time.
  • 02:41 The Format is Medium Time.
  • 02:43 Next will be tmLong. The Data Type is Date/Time.
  • 02:48 Description is Time, Long.
  • 02:52 The Default Value will be =TimeValue( Now() )
  • 03:03 Notice that as you type function names,
  • 03:07 Intellisense pops up to help you.
  • 03:09 This makes it easier when you are learning too.
  • 03:12 TimeValue is a function to get the time from a Date/Time value.
  • 03:17 Switch to Datasheet View. Save the table when prompted.
  • 03:21 Hide the columns from the previous lesson.
  • 03:24 Click in any column header where the field name is
  • 03:27 and choose Unhide Fields.
  • 03:30 Check all the date and time fields we just created.
  • 03:34 Enter Today's Date into each field.
  • 03:37 The shortcut key is Ctrl-semicolon ;
  • 03:40 Notice how format affects the way
  • 03:42 that the information is displayed?
  • 03:45 Internally, however, each field contains the same information.
  • 03:50 The tm field is formatted as a time only.
  • 03:54 There was no time entered, only a date.
  • 03:57 Therefore the time is zero, which is midnight.
  • 04:01 Date and Time are stored together.
  • 04:04 The integer portion of the number represents the date
  • 04:07 and the decimal portion represents time.
  • 04:11 When you choose various formats to display your data,
  • 04:14 the data is stored the same.
  • 04:17 Notice the last row, where you go to create a new record.
  • 04:21 The fields with a Default Value specified show a value.
  • 04:25 In the dtm field, the default value is Now()
  • 04:29 and we see Date and time.
  • 04:31 In the dt field, the default value is Date() and we see a date.
  • 04:37 In the tmLong field, the default value is an equation
  • 04:42 that strips the time component
  • 04:44 from the current date and time so we see the current time.
  • 04:48 Your dates and times will be different, of course.
  • 04:51 This is just when I am recording.
  • 04:53 Depending on what country you live in,
  • 04:55 your dates and times will look different.
  • 04:58 They will be formatted differently.
  • 05:01 Like Currency is affected by Windows Region settings,
  • 05:04 so are dates and times.
  • 05:06 In the next lesson, data types will be summarized
  • 05:09 and a few other data types such as Calculated and Attachment,
  • 05:13 that we haven't talked about yet, will be covered.

Lesson notes are only available for subscribers.

Gift this course