Date and Time Format Strings
Format strings are used as templates for converting a string into a date, time or datetime value or a date, time or datetime value into a string. The following tokens are interpreted in the format string fmt and used to read the portions of a date, time or datetime value from a string such as StringToDate and StringToDateTime.
Date Formats
| Token | Value |
|---|---|
| yyyy | 4 digit year (e.g., 2012) |
| yy | 2 digit year (e.g., 12) |
| mmmm | Full month name (e.g., January) |
| mmm | Abbreviated month name (e.g., Jan) |
| mm 1 | 2 digit month (including leading zero if appropriate). January = 01, December = 12 |
| m | 1 or 2 digit month (no leading zero). January = 1, December = 12 |
| dddd | Full day of the week name (e.g., Tuesday) |
| ddd | Abbreviated day of the week name (e.g., Tues) |
| dd | 2 digit day of the month (including leading zero if appropriate). (e.g., 04) |
| d | 1 or 2 digit day of the month (no leading zero). (e.g., 4) |
When calling DateToString, the format string fmt may contain other tokens and characters which will be passed on to the resulting string unchanged.
Examples using Date Format Strings
Given the date Tuesday, October 7, 2003 as the value of argument date, the following calls to DateToString produce the indicated results.
DateToString(date, 'mm-dd-yy') : 10-07-03
DateToString(date, 'm-d-yyyy') : 10-7-2003
DateToString(date, 'dddd, mmmm d, yyyy') : Tuesday, October 7, 2003
DateToString(date, 'Today is dddd') : Today is Tuesday
The following calls to StringToDate all produce a date value for October 7, 2003.
StringToDate('10/7/2003', 'm/d/yyyy')
StringToDate('10-07-03', 'm-dd-yy')
StringToDate('Today is 2003-Oct-03', 'Today is yyyy-mmm-dd')
Time Formats
| Token | Value |
|---|---|
| hh | 2 digit hour (e.g., 09) |
| mm 1 | 2 digit minute (e.g., 59) |
| ss | 2 digit second (e.g., 05) |
| tt | AM or PM (e.g., PM). Use single t for A or P. |
| fff | fractions of a second up to 3 places (milliseconds). Addtional places will be truncated |
Be sure to provide date and time formatting in lower case. For example, to represent hours, use hh, not HH.
If a DateTime column or Time column is used alone in an expression, it is formatted using the current locale.
If a DateTime or Time column is in an expression, its string value is TimeToNumber or DateToNumber + space + TimetoNumber.
Examples using Time and DateTime Format Strings
Given the time 10:50:30 AM as the value of argument time, the following calls to TimeToString and StringToTimeproduce the indicated results.
TimeToString(time, 'hh:mm:ss tt') : 10:50:30 AM
TimeToString(time, 'The time is hh:mm:ss tt') : The time is 10:50:30 AM.
TimeToString(time, 'hh:mm:ss.fff tt") : 10:50:30.333 AM
StringToTime('10:50:30 AM', 'hh:mm:ss tt')
StringToTime('The time is 10:50:30 AM', 'The time is hh:mm:ss tt')
StringToTime('10:50:30.333 AM', 'hh:mm"ss.fff tt")
The following examples convert a Date and Time for July 6, 2012 10:50:30 AM to a string and DateTime.
StringToDateTime('07/06/2012 10:50:30 AM', 'mm/dd/yyyy hh:mm:ss tt')
DateTimeToString(StringToDateTime('07-06-2012 10:50:30 AM', 'mm-dd-yyyy hh:mm:ss tt'), 'mm-dd-yyyy hh:mm:ss tt')