Introduction
The date and time detecion rules in strings is applied in following functions
- date()
- pure date()
- date time()
- time()
- pure time()
- Retriving values from tables with automatic date conversion enabled ( system variable table conversion[date] = true ).
Please pay attention to ambiguous dates, e.g. '10/11/12' which can be interpreted differently with different locale settings (YY/MM/DD, MM/DD/YY, DD/MM/YY in this case).
The universal YYYY-MM-DD format (e.g. 2020-12-31) is robust and shows same behavior regardless of locale settings applied.
Input format legend for following table:
- xx = 1 or 2 numeric digits
- yyyy = 4 numeric digits (year)
- MMM = min 3 letters to describe month. Multiple languages are supported.
- h = Time related suffix, not case sensitive (h, time, o'clock, am, pm, a.m., p.m., a, p). Different languages are supported, e.g. "Uhr".
am/pm and related formulation influence the hour values.
- Symbols between values, e.g. ., -, /, are not relevant unless indicated explicitly.
- Values between colons (:) relate to time info
- Spaces between letters and numbers are ignored, e.g. 23August is accepted.
| Input Formats | Examples | Outputs | Remarks |
|---|---|---|---|
| Blank | '' (e.g. in date('')) | Blank date | |
| Irrelvant information | Hello (e.g. in date(Hello)) | Blank date | Wrong contents ignored |
| Reserved keywords: today, now, utc | today, now, utc | Today's date, full date and time, and full date and time in UTC | Allowed to combine with time, e.g. 'today 13:30'. Various languages supported. |
| xx | 1, 04, 2020 | Error | Date is incomplete |
| xx xx | 10-11, 10.11, 10 11, 10/11 | Depends on local settings MDY: 2020-10-11 else: 2020-11-10 | Assumes current year |
| xx xx MMM | 10-11 April | Error | Invalid date format |
| xx xx yyyy | 10 11 2012 | Depends on local settings MDY: 2012-10-11 else: 2012-11-10 | Fully specified date. See 1 |
| xx xx xx | 10 11 12 | Depends on local settings MDY: 2012-10-11 DMY: 2012-11-10 else:2010-11-12 | Fully specified date, see 1 |
| xx xx xx yyyy | 10 11 12 2013 | Error | Invalid date format |
| xx xx xx MMM | 10 11 12 April | Error | Invalid date format |
| xx xx yyyy yyyy | 10 11 2012 2013 | Error | Invalid date format |
| xx xx yyyy MMM | 10 11 2012 April | Error | Invalid date format |
| xx xx xx h | 10 11 12 am | Time only: 10:11:12 | See 4 |
| xx xx xx h xx, xx xx xx : xx, xx xx xx : xx h | 10.11 12 h 30 | Depends on local settings MDY: 2020-10-11 13:30:00 else: 2020-11-10 12:30:00 | Assumes current year |
| xx xx xx h YYYY | 10 11 12 h 2013 | 2013-01-01, 10:11:12 | Assumes January 1, see 2 |
| xx xx xx h MMM | 10 11 12 h April | 2020-04-01, 10:11:12 | Assumes 1st day of specified month in current year, see 3 |
| xx xx xx : | 10 11 12 : | Error | Invalid date format (ambiguity) |
| xx yyyy | 10 2011 | 2011-10-01 | Assumes 1st day of specified month, see 1 |
| xx MMM | 10 April | 2020-04-10 | Assumes current year |
| xx MMM xx | 10 Nov 12 | Depends on local settings YMD: 2010-11-12 else: 2012-11-10 | Fully specified date, see 1 |
| xx MMM xx h | 10 Nov 12 h | 2020-11-10, 12:00:00 | Assumes current year, see 4 |
| xx xx h | 10 11 h, 10 11 am | Time only: 10:11:00 | |
| xx xx h xx | 10 11 h 12 | Time only: 10:11:12 | |
| xx xx h MMM | 10 11 h April | 2020-04-01 10:11:00 | Assumes 1st day of specified month in current year, see 3 |
| xx xx h yyyy | 10 11 h 2012 | 2012-01-01 10:11:00 | Assumes January 1, see 2 |
| yyyy | 2012 | 2012-01-01 | Assumes January 1 |
| yyyy xx | 2012 11 | 2012-11-01 | Assumes 1st day of specified month |
| yyyy xx xx | 2012 11 10 | 2012-11-01 | Fully specified date, see 1 |
| yyyy xx MMM | 2012 11 April | 2012-04-01 | Fully specified date, see 1 |
| yyyy xx xx h, YYYY xx xx : | 2013 12 11 h, 2013 12 11 : | Error | Invalid date format (ambiguity) |
| yyyy xx xx h xx, yyyy xx xx : xx, yyyy xx xx : xx h | 2012 11 10 h 09 | 2012-11-01 10:09:00 | Assumes 1st day of specified month |
| yyyy MMM | 2012 Nov | 2012-11-01 | Assumes 1st day of specified month |
| yyyy MMM xx | 2012 Nov 10 | 2012-11-10 | Fully specified date, see 1 |
| yyyy MMM xx h | 2012 Nov 10 am | 2012-11-01 10:00:00 | Assumes 1st day of specified month, see 4 |
| yyyy MMM xx : | 2012 Nov 10 : | Error | Invalid date format (ambiguity) |
| yyyy xx h | 2012 11 h | 2012-01-01 11:00:00 | Assumes January 1, see 4 |
| yyyy xx : | 2012 11 : | Error | Invalid date format (ambiguity) |
| yyyy xx : MMM | 2012 10 : November | 2012-11-10 | Fully specified date, see 1 |
| MMM | April | 2020-04-01 | Assumes 1st day of specified month in current year |
| MMM xx | Dec 11 | Depends on local settings MDY: 2020-12-11 else: 2011-12-01 | MDY: Assumes current year. else: Assumes 1st day of specified month |
| MMM xx xx, MMM xx yyyy, MMM xx : yyyy | Dec 11 13, Dec 11 2013, Dec 11 : 2013 | 2013-12-11 | Fully specified date, see 1 |
| MMM xx xx h, MMM xx xx : | Dec 11 h | Error | Invalid date format (ambiguity) |
| MMM xx xx h xx, MMM xx xx h xx : MMM xx xx : xx, MMM xx xx : xx | Oct 11 12 h 13, Oct 11 12 h 13: Oct 11 12:13, Oct 11 12 h 13: | Depends on local settings MDY: 2020-10-11 12:13:00 else: 2011-10-01 12:13:00 | MDY: Assumes current year. else: Assumes 1st day of specified month, see 5 |
| MMM yyyy | Dec 11 | 2020-12-11 | Assumes current year, see 1 |
| MMM xx h | Dec 11 h | 2020-12-01 11:00:00 | Assumes 1st day of specified month in current year, see 4 |
| MMM xx : | Dec 11 : | Error | Invalid date format (ambiguity) |
| MMM xx : xx, MMM xx : xx h, MMM xx : xx : | Dec 11 : 10 | 2020-12-01 11:10:00 | Assumes January 1, see 5 |
| xx h, xx : | 15 h, 15 : | Time only: 15:00:00 | See 4 |
| xx h xx, xx h xx : xx : xx, xx : xx h, xx : xx : | 15h30, 15h30: 15:30h, 15:30: | Time only: 15:30:00 | See 5 |
| xx h xx xx, xx : xx xx | 15 h 20 30, 15 : 20 30 | Time only: 15:20:30 | |
| xx h xx : xx, xx : xx : xx xx : xx h xx, xx : xx xx h xx : xx : xx h | 15h20:30, 15:20:30 15:20h30 15h20 30h, 15:20:30h | Time only: 15:30:30 | See 6 |
| Above values with additional colon behind | e.g. 15 h 20 30 : | Error | Invalid time format |
| xx h yyyy | 15 h 2020 | 2020-01-01 15:00:00 | Assumes January 1, see 2 |
| xx h MMM | 15 h October | 2020-10-01 15:00:00 | Assumes 1st day of specified month in current year, see 3 |
| xx : xx yyyy, xx : xx h yyyy | 15 : 20 2020, 15 : 20 h 2020 | 2020-01-01 15:20:00 | Assumes 1st day of specified month in current year, see 2 |
| xx h xx yyyy | 15 h 20 2020 | Error | Invalid time format |
| xx : xx : yyyy, xx : xx : MMM | 15 : 30 : 2020 | Error | Invalid time format |
| xx : xx MMM, xx : xx h MMM | 15 : 20 Oct, 15 : 20 h Oct | 2020-01-01 15:20:00 | Assumes 1st day of specified month in current year, see 2 |
| ... MMM ... MMM ... Multiple months identified | 15 Jan Feb 2020 | Error | Invalid time format |
| ... yyyy ... yyyy Multiple years identified | 13. Feb 2019 or 2020 | Error | Invalid time format |
| Year is not 00-99 and not 1900-9999 | 04. July 1776 | Error | Invalid year value (before 1900) |
| Month is not 01-12 | 2012-13-14 | Error | Invalid month value |
| Day is out of range 1-28,29,30,31 | 2013-02-29 | Error (Leapyear: 2012-02-09 would be correct) | Invalid day value |
| Time values out of range hours: 1-23, min,sec: 0-59 | 24:00:00, 15:60:00 | Error | Invlaid time value |
| More than 6 values recognized | 2012 11 10 15:20:30 40 | Error | Invalid date and time format |
| Multiple time-related suffices | 10:30 h p.m. | Error (Either use 'h' or 'p.m.') | Invalid time format |
1 1-3 additional numbers following afterwards are recognized as hours, minutes and seconds
2 The date has been started with year. Month and day may be specified afterwards to complete the date
3 The date has been started with month. Day and year may be specified afterwards to complete the date
4 The date has been completed with hour. Minute and second values may be specified afterwards to complete the time
5 The date has been completed with hour and minute. Second value may be specified afterwards to complete the time
6 A complete time has been specified in a valid and unambiguous format. A date may follow afterwards, e.g. '20:30:40 2020-02-02'
Some code examples
set locale( en_us );
echo( date("10-11-12 8:15p") );
set locale( de_ch );
echo( date("10-11-12 8:15p") );
2012-10-11 20:15:00
2012-11-10 20:15:00