Character | Description |
| : | Time separator. Separates hours, minutes, and seconds when time values are formatted. The actual character used as the time separator in formatted output is determined by your system settings. |
| / | Date separator. Separates the day, month, and year when date values are formatted. The actual character used as the date separator in formatted output is determined by your system settings. |
| c | Display the date as ddddd and display the time as ttttt, in that order. Display only date information if there is no fractional part to the date serial number; display only time information if there is no integer portion. |
| d | Display the day as a number without a leading zero (1 – 31). |
| dd | Display the day as a number with a leading zero (01 – 31). |
| ddd | Display the day as an abbreviation (Sun – Sat). |
| dddd | Display the day as a full name (Sunday – Saturday). |
| ddddd | Display the date as a complete date (including day, month, and year), formatted according to your system’s short date format setting. The default short date format is m/d/yy. |
| dddddd | Display a date serial number as a complete date (including day, month, and year) formatted according to the long date setting recognized by your system. The default long date format is mmmm dd, yyyy. |
| aaaa | The same as dddd, only it’s the localized version of the string. |
| w | Display the day of the week as a number (1 for Sunday through 7 for Saturday). |
| ww | Display the week of the year as a number (1 – 54) This is not reliable, see notes below. |
| m | Display the month as a number without a leading zero (1 – 12). If m immediately follows h or hh, the minute rather than the month is displayed. |
| mm | Display the month as a number with a leading zero (01 – 12). If m immediately follows h or hh, the minute rather than the month is displayed. |
| mmm | Display the month as an abbreviation (Jan – Dec). |
| mmmm | Display the month as a full month name (January – December). |
| oooo | The same as mmmm, only it’s the localized version of the string. |
| q | Display the quarter of the year as a number (1 – 4). |
| y | Display the day of the year as a number (1 – 366). |
| yy | Display the year as a 2-digit number (00 – 99). |
| yyyy | Display the year as a 4-digit number (100 – 9999). |
| h | Display the hour as a number without leading zeros (0 – 23). |
| Hh | Display the hour as a number with leading zeros (00 – 23). |
| N | Display the minute as a number without leading zeros (0 – 59). |
| Nn | Display the minute as a number with leading zeros (00 – 59). |
| S | Display the second as a number without leading zeros (0 – 59). |
| Ss | Display the second as a number with leading zeros (00 – 59). |
| t t t t t | Display a time as a complete time (including hour, minute, and second), formatted using the time separator defined by the time format recognized by your system. A leading zero is displayed if the leading zero option is selected and the time is before 10:00 A.M. or P.M. The default time format is h:mm:ss. |
| AM/PM | Use the 12-hour clock and display an uppercase AM with any hour before noon; display an uppercase PM with any hour between noon and 11:59 P.M. |
| am/pm | Use the 12-hour clock and display a lowercase AM with any hour before noon; display a lowercase PM with any hour between noon and 11:59 P.M. |
| A/P | Use the 12-hour clock and display an uppercase A with any hour before noon; display an uppercase P with any hour between noon and 11:59 P.M. |
| a/p | Use the 12-hour clock and display a lowercase A with any hour before noon; display a lowercase P with any hour between noon and 11:59 P.M. |
| AMPM | Use the 12-hour clock and display the AM string literal as defined by your system with any hour before noon; display the PM string literal as defined by your system with any hour between noon and 11:59 P.M. AMPM can be either uppercase or lowercase, but the case of the string displayed matches the string as defined by your system settings. The default format is AM/PM. |
Format | Description |
|---|---|
| General Date | Display a date and/or time. For real numbers, display a date and time, for example, 4/3/12 05:34 PM.If there is no fractional part, display only a date, for example, 4/3/12. If there is no integer part, display time only, for example, 05:34 PM. Date display is determined by your system settings. |
| Long Date | Display a date according to your system’s long date format. |
| Medium Date | Display a date using the medium date format appropriate for the language version of the host application. |
| Short Date | Display a date using your system’s short date format. |
| Long Time | Display a time using your system’s long time format; includes hours, minutes, seconds. |
| Medium Time | Display time in 12-hour format using hours and minutes and the AM/PM designator. |
| Short Time | Display a time using the 24-hour format, for example, 17:45. |
When using Access to insert data into a SQL Server table, it is recommended to use the YYYYMMDD hh:nn:ss format, this minimises problems with international date/time formatting.
The option for weeknumbers (ww) is buggy for certain weeks/years. To produce a correct ISO 8601 weeknumber use a function, if this is saved in a module it can be used in VBA or in an SQL query:
Function ISOWeekNum(dtmDate) ' Returns a WeekNumber from a date Dim NearThurs NearThurs = ((dtmDate+5) \ 7) * 7 - 2 ISOWeekNum = ((NearThurs - DateSerial(Year(NearThurs), 1, 1)) \ 7) + 1 End function ' Example wscript.echo "ISOWeekNumber: " & ISOWeekNum(cdate("2017-12-25"))
“The aim of science is not to open the door to infinite wisdom but to set a limit to infinite error” ~ Bertolt Brecht ‘Life of Galileo’
Number formats
Format function
Standard date and time notation - YYYY-MM-DD
Q200299 - BUG: Format or DatePart Functions return wrong Week number for last Monday in year.