FORMAT with a DateTime argument

The FORMAT function formats a timestamp according to the specified format mask.

Syntax

String FORMAT(dateTime DateTime, 'mask String')

Arguments

• dateTime

  A DateTime expression to format.

• mask

  Format-control string. See the following tables.

  Note: The FORMAT function also works with a numeric argument. For more information, see FORMAT with a numeric argument.

  Standard format options

  You can specify a standard format using one of the following characters in the format mask:

  Character	Results
  d	Displays the short date. For example, for 2018-06-15 14:15:30, returns 6/15/2018.
  D	Displays the long date. For example, for 2018-06-15 14:15:30, returns Friday, June 15, 2018.
  f	Displays the full date and short time. For example, for 2018-06-15 14:15:30, returns Friday, June 15, 2018 2:15 PM.
  F	Displays the full date and long time. For example, for 2018-06-15 14:15:30, returns Friday, June 15, 2018 2:15:30 PM.
  g	Displays the general date and short time. For example, for 2018-06-15 14:15:30, returns 6/15/2018 2:15 PM.
  G	Displays the general date and long time. For example, for 2018-06-15 14:15:30, returns 6/15/2018 2:15:30 PM.
  m or M	Displays the month and day portion of the date. For example, for 2018-06-15 14:15:30, returns June 15.
  t	Displays the short time. For example, for 2018-06-15 14:15:30, returns 2:15 PM.
  T	Displays the long time. For example, for 2018-06-15 14:15:30, returns 2:15:30 PM.

  Note: For more details, see the Microsoft article: Standard Date and Time Format Strings.

  Custom mask options

  You can specify a custom format using the following characters in the format mask

  Character	Results
  d or dd	Represents the day of the month as one or two digits. For one digit, use a single placeholder. For two digits, use two placeholders.
  ddd	Abbreviates the name of the day. For example, for Friday, the ddd mask returns Fri.
  dddd	Represents the full name of the day of the week.
  M or MM	Represents the month as either a one-digit or two-digit number.
  MMM	Abbreviates the name of the month. For example, for October, the MMM mask returns Oct.
  MMMM	Spells out the full month name.
  y	Represents the year as a one-digit or two-digit number. If the year has more than two digits, the two low-order digits are used. If the first digit of a two-digit year is a zero, the leading zero is not included. For example, 2004 is displayed as 4.
  yy	Represents the last two digits of the year. If the first digit of a two-digit year is a zero, the leading zero is included. For example, 2004 is displayed as 04.
  yyy	Represents the year with a minimum of three digits. If the year has more than three significant digits, they are included in the result string. If the year has fewer than three digits, the number is padded with leading zeros to produce three digits.
  yyyy	Represents the year with four digits. If the year has more than four significant digits, they are included in the result string. If the year has fewer than four digits, the number is padded with leading zeros to produce four digits.
  yyyyy	Represents the year with five digits. If the year has fewer than five digits, the number is padded with leading zeros to produce five digits.
  H or HH	Represents the hour as one or two digits. 24-hour format.
  h or hh	Represents the hour as one or two digits. 12-hour format.
  tt	Represents the half of day indicator for 12-hour clock values, such as AM or PM.
  t	Represents the first character of the half of day indicator for 12-hour clock values, such as A or P.
  m or mm	Represents minutes as one or two digits.
  s or ss	Represents seconds as one or two digits.
  f, ff, fff, ffff, fffff, ffffff, or fffffff	Represents subsecond fractions to the number of digits specified, up to 7 digits. If the f mask is used alone without other custom mask specifiers, it is considered the f standard mask, which displays the full date and short time.
  F, FF, FFF, FFFF, FFFFF, FFFFFF, or FFFFFFF	Represents subsecond fractions to the number of digits specified, up to 7 digits. Nothing is displayed if the digit is zero. If the F mask is used alone without other custom mask specifiers, it is considered the F standard mask, which displays the full date and long time.
  F*	Represents the fraction portion of the timestamp, up to 7 digits, if it is not zero. If the fraction portion is zero, the previous separator and the fraction are omitted.
  g or gg	Represents the period or era, such as A.D. If the date does not have an associated period or era, this portion of the mask is ignored. If the g mask is used alone without other custom mask specifiers, it is considered the g standard mask, which displays the general date and short time.
  :	Represents the time separator used to differentiate hours, minutes, and seconds.
  /	Represents the date separator used to differentiate years, months, and days.
  / \ : - . , (forward slash, backward slash, colon, dash, period, comma)	Characters, including spaces, allowed as a separator. For / and :, to ensure that the actual character is used instead of replaced by the date separator or time separator, wrap the character in single quotes as follows: '/' and ':'.

Examples

• SELECT Format(MyDateTime, 'h:m:s') - Returns 5:4:23

• SELECT Format(MyDateTime, 'hh:mm:ss tt') - Returns 05:04:23 PM

• SELECT Format(MyDateTime, 'MMM d yyyy') - Returns Jan 27 2018

• SELECT Format(MyDateTime, 'HH:mm:ss') - Returns 17:04:23

• SELECT Format(MyDateTime, 'hh:mm:ss.fff') - Returns 05:04:23.000