TodateEx

The MDX TodateEx function for Essbase returns the numeric date value from input date-string according to the date-format specified.

The date returned is the number of seconds elapsed since midnight, January 1, 1970.

If the date or the date format strings are invalid, an error is returned.

Syntax

TodateEx ( internal-date-format, date-string )

Parameters

internal-date-format

One of the following literal strings (excluding ordered-list numbers and parenthetical examples) indicating a supported date format.

1. "mon dd yyyy" (Example: mon = Aug)

2. "Month dd yyyy" (Example: Month = August)

3. "mm/dd/yy"

4. "mm/dd/yyyy"

5. "yy.mm.dd"

6. "dd/mm/yy"

7. "dd.mm.yy"

8. "dd-mm-yy"

9. "dd Month yy"

10. "dd mon yy"

11. "Month dd, yy"

12. "mon dd, yy"

13. "mm-dd-yy"

14. "yy/mm/dd"

15. "yymmdd"

16. "dd Month yyyy"

17. "dd mon yyyy"

18. "yyyy-mm-dd"

19. "yyyy/mm/dd"

20. Long format (Example: WeekDay, Mon dd, yyyy)

21. Short format (Example: m/d/yy)

date-string

A date string following the rules of internal-date-format. The following examples correspond to the above listed internal date formats.

1. Jan 15 2006

2. January 15 2006

3. 01/15/06

4. 01/15/2006

5. 06.01.06

6. 15/01/06

7. 15.01.06

8. 15-01-06

9. 15 January 06

10. 15 Jan 06

11. January 15 06

12. Jan 15 06

13. 01-15-06

14. 06/01/15

15. 060115

16. 15 January 2006

17. 15 Jan 2006

18. 2006-01-15

19. 2006/01/15

20. Sunday, January 15, 2006

21. 1/8/06 (m/d/yy)

Notes

• This function is an extension of Todate.

• This function is case-sensitive. For example, using apr instead of Apr returns an error.

• Using extra whitespace not included in the internal format strings returns an error.

• Trailing characters after the date format has been satisfied are ignored. If you erroneously use a date string of 06/20/2006 with date format mm/dd/yy, the trailing 06 is ignored and the date is interpreted as June 20, 2020.

• Long Format (Weekday, Mon dd, yyyy) is not verified for a day-of-week match to the given date.

  For example: For date string Sunday, March 13, 2007 with date format Long Format, the input date string is parsed correctly for March 13, 2007, although March 13, 2007 does not fall on Sunday.

• If you specify a date that is earlier than 01-01-1970, this function returns an error.

• The latest date supported by this function is 12-31-2037.

• When the yy format is used, this function interprets years in the range 1970 to 2029.

Example

The following query returns the actual sales on May 31, 2005 for the product Cola in the market California.

TodateEx() returns the date May 31, 2005, corresponding to date string 05.31.2005. StrToMbr returns the corresponding day level member, capturing May 31, 2005.

SELECT
 {[Sales]}
ON COLUMNS,
 {
   StrToMbr(
        FormatDate(
                TodateEx("mm.dd.yyyy", "05.31.2005"),
                "Mon dd yyyy"
        )
   ) 
 }
ON ROWS
FROM Mysamp.basic
WHERE (Actual, California, Cola);