Character | Description |
: | Time separator. Separates hours, minutes, and seconds when time values are formatted. |
/ | Date separator. Separates the day, month, and year when date values are formatted. |
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. |
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’
Related:
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.