Today, we explore an interesting and vital function within Google Data Studio - the DAY function. This practical yet straightforward tool provides key insights by evaluating and returning the day of the month from any given date.
Understanding The Syntax
There are two different ways to utilize the DAY function, contingent on the data you have:
-
Extracting the day from a Date or Date & Time value:
DAY(date_expression)
-
Extracting the day from a compatibility mode Date value:
DAY(X [,Input Format])
In both instances:
-
date_expression
refers to a field or expression that is either in Date or Date & Time format.
-
X
can be a field or expression which translates to Text, Number, or compatibility mode Date.
-
Input Format
offers a formatting option for X
. It's optional if X
is correctly structured as a semantic date field.
Gearing Up the DAY Function
Let's now dissect how to use the DAY function.
When extracting the day from a Date or Date & Time value, you simply use the
DAY(date_expression)
syntax and input your date expression.
On the other hand, when dealing with the compatibility mode Date value, you should use the
DAY(X [,Input Format])
syntax. Many input formats are accepted such as
%Y/%m/%d-%H:%M:%S
and
%Y-%m-%d [%H:%M:%S]
, among others, whenever your
X
is a Text field or expression, or compatibility mode Date. For Number fields or expressions, DAY accepts
SECONDS
,
MILLIS
,
MICROS
,
NANOS
, or
JULIAN_DATE
.
Examples in Action
Let's apply this knowledge with some practical sales metrics:
Suppose we have a sales record date and we want to determine the sales day:
-
For a Date or Date & Time value:
DAY("2025/12/25")
This will return 25
, the day of the month for the date provided.
-
For compatibility mode Date value with Text field:
DAY("20211225", "DEFAULT_DECIMAL")
This returns
25
, recognizing December 25th as the day of the month.
- For compatibility mode Date value with Number field:
DAY(1609459200, "SECONDS")
This will return
1
as it recognizes January 1, 2021 when the seconds since the Unix Epoch time is converted.
Function Limitations
While hugely beneficial, the DAY function does have some limitations. Due to its design, it only returns the 'day' part of a date; thus, it can't be used to extract other components like month or year from a date. Additionally, it works optimally with compatibility mode dates and upgraded Date and Date & Time data types.
Tips & Tricks
To maximize the DAY function, ensure to correctly identify whether your data is in Date or Date & Time format, a compatibility mode Date value, or a Number field. Utilizing the correct syntax is crucial for the correct output. Additionally, knowing and understanding the various formatting available for
X
in the compatibility mode DATE will glean accurate results.
The DAY function, while elementary in Google Data Studio, deciphers compelling insights from our date data. Breathing proficiency into this function allows for better interpretation and comprehension of your data sets.
.png)
