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')
1 When both month and minutes are used, the first mm is assumed to be the month. Consider using date and time formatting separately to avoid confusion.