Search Results

Custom formatting of date and time columns

If the format you want to use cannot be created with the given settings, the custom format string allows you to create your own formats using a code explained in the examples below.

Examples:

Below are some examples of custom format strings for datetime formats. For more information, see literature about custom DateTime format strings, such as that on MSDN.
Note: If you want to use any of the custom date and time format specifiers alone in a format string (for example, to use the "d", "h", or "M" specifier by itself), you must either add a space before or after the specifier, or include a percent sign ("%") before the single custom date and time specifier, to avoid it being interpreted as a standard format string.
Character Description
yy Returns the year, measured as a number between 0 and 99.
yyyy Returns the year as a four-digit number.
M Returns the month, measured as a number between 1 and 12, with one or two digits depending on the value.

This means that June will be written as '6', when this format string is applied (whereas November is written as '11').

MM Returns the month with two digits, measured as a number between 1 and 12. This means that June will be written as '06'.
MMM Returns the abbreviated name of the month. For example, 'Jun'.
MMMM Returns the full name of the month. For example, 'June'.
d Returns the day of the month, measured as a number between 1 and 31, with one or two digits depending on the value.

This means that the 6th of a month will be written as '6' (whereas the 11th is written as '11'), when this format string is applied.

dd Returns the day of the month with two digits, measured as a number between 1 and 31. This means that the 6th of a month will be written as '06'.
ddd Returns the abbreviated name of the day of the week. For example, 'Fri'.
dddd Returns the full name of the day of the week. For example, 'Friday'.
h Returns the hour using a 12-hour clock, with one or two digits depending on the value.

This means that if the time is 7:20:22, then the format 'h' will display the hour as '7', that is, using a single digit. If the time is 11:20:22, then 'h' returns 11.

hh Returns the hour using a 12-hour clock which always uses two digits.

This means that the time 7:20:22 will be written as '07:20:22' when the 'hh' format string is applied.

H Returns the hour using a 24-hour clock, with one or two digits depending on the value.

This means that if the time is 7:20:22, then the format 'H' will display the hour as '7', that is, using a single digit. If the time is 11:20:22, then 'H' returns 11. If the time is 20 minutes past seven in the evening, then 'H' returns 19:20:00.

HH Returns the hour using a 24-hour clock which always uses two digits. This means that 6 o'clock in the morning will be written as '06' and 6 o'clock in the evening will be written as '18'.
m Returns the minute with one or two digits, depending on the value. This means that six minutes will be written as '6' (whereas 11 minutes are written as '11').
mm Returns the minute with two digits. This means that six minutes will be written as '06'.
s Returns the second with one or two digits, depending on the value.

This means that six seconds will be written as '6' (whereas 11 seconds are written as '11').

ss Returns the second with two digits. This means that six seconds will be written as '06'.
f Returns the tenths of a second.
ff Returns the hundredths of a second.
fff Returns the milliseconds.
tt Returns the AM/PM designator.
: Returns the time separator.
/ Returns the date separator.

You can also add any custom string value, but if any of the specifier characters are included in the string, they need to be escaped by a backslash (\).

Note: All the examples below use the following value from the data: Friday, October 16, 2009, at 25 minutes past three in the afternoon.
Format string Result
dd\t\h o\f MMMM yyyy 16th of October 2009
MMM d yyyy, HH:mm Oct 16 2009, 15:25
\year: YY, \mon\t\h: MM, \da\y: dd year: 09, month: 10, day: 16
hh:mm tt 03:25 PM
m \minu\te\s pa\s\t h, MMM d 25 minutes past 3, Oct 16
%d 16