DateTime Parts
This page is split into two parts to deal with (a) formatting date parts, (b) using date parts as the unit in datediff calculations.
Part One
The following are the date/time options when formatting or parsing date(time)s.
NB. Date formatting in OrgVue is set by the format dropdown in the Edit Property dialogue, and Date formatting in Tableau can be done within the Format pane:
Format
Excel
Tableau
Alteryx
Python
Example
Year (4 digits)
yyyy
year
%Y
%Y
2016
Year (2 digits)
yy
n/a
%y
%y
16
Quarter
n/a
quarter
n/a
n/a
01 : 04
Month (number)
mm
month
%m
%m
01 : 12
Month name (abbreviated)
mmm
month
%b
%b
Jan : Dec
Month name (long form)
mmmm
month
%B
%B
January : December
Week (number) - beginning on Monday
Not a formatting option; use =~WEEKNUM!~(‹date›)
week
%W
%W
01 : 53
Week (number) - beginning on Sunday
n/a
n/a
%U
%U
01 : 53
Day of the month
dd
day
%d
%d
01 : 31
Day of the year
n/a
dayofyear
%j
%j
001 : 366
Weekday (number)
n/a
n/a
n/a
%w
01 : 07
Weekday name (abbreviated)
ddd
weekday
%a
%a
Mon : Sun
Weekday name (long form)
dddd
weekday
%A
%A
Monday : Sunday
Hour on a 24-hr clock
hh
hour
%H
%H
00 : 23
Hour on a 12-hr clock
n/a
n/a
%I
%I
01 : 12
Minute
(hh:)mm
minute
%M
%M
00 : 59
Second
(hh:mm:)ss
second
%S
%S
00 : 59
Microsecond
n/a
n/a
n/a
%f
0000 : 9999
Period
n/a
n/a
%p
%p
AM : PM
Add period
hh:mm:ss AM/PM
n/a
%X
n/a
datetime + AM : PM
Full formatted date
n/a
n/a
%D
n/a
Equivalent to %m/%d/%y.
Full formatted time
n/a
n/a
%T
n/a
Equivalent to %H:%M:%S.
The date for the computer's locale.
n/a
n/a
%x
%x
[Date object]
The datetime for the computer’s locale.
n/a
n/a
%c
%c
[Date object]
The time of the computer's locale
n/a
n/a
n/a
%X
[Date object]
Name of timezone
n/a
n/a
%Z
%Z
UTC, EST etc.
Part Two
The following are the date/time options when writing a datediff or age type calculation:
Date Part
Min : Max Values
Excel
Tableau
Alteryx
OrgVue
Years
-∞ : ∞
"Y"
'year'
'year' / 'years'
'y'
Quarters
-∞ : ∞
n/a
'quarter'
n/a
'q'
Months
-∞ : ∞
"M"
'month'
'month' / 'months'
'm'
Months (irrespective of the year of the two dates)
0 : 11
"YM"
n/a
n/a
n/a
Days
-∞ : ∞
"D"
'day'
'day' / 'days'
'd'
Days (irrespective of month & year of the two dates)
0 : 30
"MD"
n/a
n/a
n/a
Days (irrespective of the year of the two dates)
0 : 364
"YD"
n/a
n/a
n/a
Hours
-∞ : ∞
n/a
'hour'
'hour' / 'hours'
n/a
Minutes
-∞ : ∞
n/a
'minute'
'minute' / 'minutes'
n/a
Seconds
-∞ : ∞
n/a
'second'
'second' / 'seconds'
n/a
Last updated
Was this helpful?