TimestampFormat
Format a TIMESTAMP variable into a string.
Syntax
TimestampFormat(TIMESTAMP Timestamp, STRING Format [, INT UTC])
Timestamp:
The timestamp variable.
Format:
The format of the string is the same as .NET Framework DateTime format. Specifically be reminded that the format is case sensitive. For example 'MM' is the zero padded month number, whereas 'mm' is the zero padded current minute within the hour. Therefore no Year, Day or Seconds will be displayed if they are specified in uppercase as: YYYY, DD, SS. The correct display will only occur when they are specified in lowercase as: yyyy, dd, ss.
A short list of formats are included below extracted from the"Custom Date and Time Format Strings" in the Microsoft .NET Framework Developer's Guide from the MSDN Library. It is recommended that you confirm its currency by consulting the source information periodically.
Use the following single-character format strings by themselves to choose predefined standard date and time formats:
- d Short date pattern. Ex. "6/15/2009"
- D Long date pattern. Ex. "Monday, June 15, 2009"
- f Full date/time pattern (short time). Ex. "Monday, June 15, 2009 1:45 PM"
- F Full date/time pattern (long time). Ex. "Monday, June 15, 2009 1:45:30 PM"
- g General date/time pattern (short time). Ex. "6/15/2009 1:45 PM"
- G General date/time pattern (long time). Ex. "6/15/2009 1:45:30 PM"
- M or m Month/day pattern. "June 15"
- O or o Round-trip date/time pattern. Ex. "2009-06-15T13:45:30.0900000"
- R or r RFC1123 pattern. Ex. "Mon, 15 Jun 2009 20:45:30 GMT"
- s Sortable date/time pattern. Ex. "2009-06-15T13:45:30"
- t Short time pattern. Ex. "1:45 PM"
- T Long time pattern. Ex. "1:45:30 PM"
- u Universal sortable date/time pattern. Ex. "2009-06-15 20:45:30Z"
- U Universal full date/time pattern. Ex. "Monday, June 15, 2009 8:45:30 PM"
- Y or y Year month pattern. Ex. "June, 2009"
For all the examples above, the input time/date is assumed to be 6/15/2009 1:45:30 PM with Windows set to the en-US locale. Many of the formats will change according to the current Windows locale.
You may combine the following format specifiers to make up custom format specifications:
- d The day of the month, from 1 through 31.
- dd The day of the month, from 01 through 31.
- ddd The abbreviated name of the day of the week.
- dddd The full name of the day of the week.
- f Fraction of a second. Returns one decimal place for each 'f', up to 'fffffff'
- F Fraction of a second, if non-zero. Returns one decimal place for each 'f', up to 'fffffff'
- g, gg The period or era.
- h The hour, using a 12-hour clock from 1 to 12.
- hh The hour, using a 12-hour clock from 01 to 12.
- H The hour, using a 24-hour clock from 0 to 23.
- HH The hour, using a 24-hour clock from 00 to 23.
- K Time zone information.
- m The minute, from 0 through 59.
- mm The minute, from 00 through 59.
- M The month, from 1 through 12.
- MM The month, from 01 through 12.
- MMM The abbreviated name of the month.
- MMMM The full name of the month.
- s The second, from 0 through 59.
- ss The second, from 00 through 59.
- t The first character of the AM/PM designator.
- tt The AM/PM designator.
- y The year, from 0 to 99.
- yy The year, from 00 to 99.
- yyy The year, with a minimum of three digits.
- yyyy The year as a four-digit number.
- yyyyy The year as a five-digit number.
- z Hours offset from UTC, with no leading zeros.
- zz Hours offset from UTC, with a leading zero for a single-digit value.
- zzz Hours and minutes offset from UTC.
- : The time separator.
- / The date separator.
- "string" or 'string' Literal string delimiter.
- % Defines the following character as a custom format specifier.
- \ The escape character.
- Any other character The character is copied to the result string unchanged.
UTC:
Coordinated Universal Time (optional):
0 - Returns the time as a local date/time (default).
1 - Returns the time as a UTC date/time.
Return Value
A string containing the converted time/date, or an empty string if invalid.
Related Functions
TimestampAdd, TimestampCurrent, TimestampDifference, TimestampSub, TimestampToStr, TimestampCreate, TimestampGetPart, TimestampToTimeInt, TimeIntToTimestamp
Example
TIMESTAMP t1 = TimestampCreate(2009,07,11,09,27,34,123);
STRING sTimestamp = TimestampFormat(t1, "dd/MM/yyyy hh:mm:ss.fff");
// sTimestamp equals "11/07/2009 09:27:34.123"
See Also
Timestamp Functions
Published June 2018