|
Valid Format Strings for Dates in BI
When using MDX
Here is a list of other valid format strings which can be used when applying an MDX script for a Date Field:
|
NOTE:
- The resulting format for dates will depend on the selected locality.
- The examples shown below are based on the Australian locality.
|
Input Field is Date Picker
|
Input Field is Component Lookup
|
Example for Resulting
Date Output/Format
|
"Standard" MDX format
- mdxDate,year
- mdxDate,quarter
- mdxDate,month
- mdxDate,day
"Customised" MDX formats
- mdxDate,[dd]-[M]-[##]-[yy]
"Standard" Date Formats
- date
- date,short
- date,medium
- date,long
- date,full
- time
- time,short
- time,medium
- time,long
- time,full
- datetime,medium
- datetime,long
- datetime,full
|
- {0,mdxDate,year}
- {0,mdxDate,quarter}
- {0,mdxDate,month}
- {0,mdxDate,day}
- mdxDate,[dd]-[M]-[##]-[yy]
- {0,date}
- {0,date,short}
- {0,date,medium}
- {0,date,long}
- {0,date,full}
- {0,time}
- {0,time,short}
- {0,time,medium}
- {0,time,long}
- {0,time,full}
- {0,date,medium}
- {0,datetime,long}
- {0,datetime,full}
|
Resulting Output
- [2015]
- [2015].[Q1]
- [2015].[Q1].[Feb]
- [2015].[Q1].[Feb].[5]
Resulting Output
- 5 Feb 2015
- 05/02/2015
- 5 Feb 2015
- 5 February 2015
- Thursday, 5 February 2015
- 7:15:30 AM
- 7:15 AM
- 7:15:30 AM
- 7:15:30 AM
- 7:15:30 AM AST
- 07/02/2015 7:15:30 AM
- 5 February 2015 7:15:30 AM
- Thursday, 5 February 2015 7:15:30 AM
|
When intending to apply a different format for a Date Field
When applying a different format to a date parameter, then any of these patterns can be used.
|
NOTE:
- Please be aware that the patterns are CASE sensitive.
|
Letter
|
Date or Time Component
|
Presentation
|
Examples
|
G
|
Era designator
|
Text
|
AD
|
y
|
Year
|
Year
|
1996; 96
|
Y
|
Week year
|
Year
|
2009; 09
|
M
|
Month in year
|
Month
|
July; Jul; 07
|
w
|
Week in year
|
Number
|
27
|
W
|
Week in month
|
Number
|
2
|
D
|
Day in year
|
Number
|
189
|
d
|
Day in month
|
Number
|
10
|
F
|
Day of week in month
|
Number
|
2
|
E
|
Day name in week
|
Text
|
Tuesday; Tue
|
u
|
Day number of week (1 = Monday, ..., 7 = Sunday)
|
Number
|
1
|
a
|
Am/pm marker
|
Text
|
PM
|
H
|
Hour in day (0-23)
|
Number
|
0
|
k
|
Hour in day (1-24)
|
Number
|
24
|
K
|
Hour in am/pm (0-11)
|
Number
|
0
|
h
|
Hour in am/pm (1-12)
|
Number
|
12
|
m
|
Minute in hour
|
Number
|
30
|
s
|
Second in minute
|
Number
|
55
|
S
|
Millisecond
|
Number
|
978
|
z
|
Time zone
|
General time zone
|
Pacific Standard Time; PST; GMT-08:00
|
Z
|
Time zone
|
RFC 822 time zone
|
-0800
|
X
|
Time zone
|
ISO 8601 time zone
|
-08; -0800; -08:00
|
Here are some examples:
Date and Time Pattern
|
Result
|
"yyyy.MM.dd G 'at' HH:mm:ss z"
|
2001.07.04 AD at 12:08:56 PDT
|
"EEE, MMM d, ''yy"
|
Wed, Jul 4, '01
|
"h:mm a"
|
12:08 PM
|
"hh 'o''clock' a, zzzz"
|
12 o'clock PM, Pacific Daylight Time
|
"K:mm a, z"
|
0:08 PM, PDT
|
"yyyyy.MMMMM.dd GGG hh:mm aaa"
|
02001.July.04 AD 12:08 PM
|
"EEE, d MMM yyyy HH:mm:ss Z"
|
Wed, 4 Jul 2001 12:08:56 -0700
|
"yyMMddHHmmssZ"
|
010704120856-0700
|
"yyyy-MM-dd'T'HH:mm:ss.SSSZ"
|
2001-07-04T12:08:56.235-0700
|
"yyyy-MM-dd'T'HH:mm:ss.SSSXXX"
|
2001-07-04T12:08:56.235-07:00
|
"YYYY-'W'ww-u"
|
2001-W27-3
|
(The above examples originate from the following site: http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html)
|
