Google Sheets QUERY: Scalar Functions

Scalar functions include date and time functions and text functions.

These functions can be used with: SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LABEL, and FORMAT.

PIVOT, LABEL, and FORMAT will be explained later in this course.

YEAR

The YEAR function returns the year of a date.

For example, to return rank 2 users (D) whose registration date (B) is 2023, enter:

=QUERY(DB!A1:G15,"SELECT * WHERE YEAR(B) = 2023 AND D = 2")

MONTH

The MONTH function returns the month of a date.

For example, to return users whose registration (B) was made in June, enter:

=QUERY(DB!A1:G15,"SELECT * WHERE MONTH(B) = 5")

DAY

The DAY function returns the day of a date.

For example, to return the registration date (B), the registration day (B), the username (C) and limit the display to 10 results, enter:

=QUERY(DB!A1:G15,"SELECT B, DAY(B), C LIMIT 10")

HOUR

The HOUR function returns the hour alone (without minutes or seconds).

For example, to return data whose hour (F) is greater than or equal to 12, enter:

=QUERY(E1:G7,"SELECT * WHERE HOUR(F) >= 12")

MINUTE

The MINUTE function returns the minutes of data containing an hour (its operation is similar to the previous date and time functions).

SECOND

The SECOND function returns the seconds of data containing an hour (its operation is similar to the previous date and time functions).

QUARTER

The QUARTER function returns the quarter of a date (from 1 to 4).

For example, to return the quarter (B) and the sum of messages (E) by quarter (B), enter:

=QUERY(DB!A1:G15,"SELECT QUARTER(B), SUM(E) GROUP BY QUARTER(B)")

In this case, only one row is returned because there is only one quarter for all dates.

DAYOFWEEK

The DAYOFWEEK function returns the day of the week of a date (from 1 to 7, Sunday = 1, Monday = 2, etc).

For example, to return the day of the week (B) and the number of registrations (B) per day of the week (B), enter:

=QUERY(DB!A1:G15,"SELECT DAYOFWEEK(B), COUNT(B) GROUP BY DAYOFWEEK(B)")

NOW

The NOW function returns the current date and time (and can be used with other date and time functions).

For example, to return the number of registrations (B) for the current year, enter:

=QUERY(DB!A1:G15,"SELECT COUNT(B) WHERE YEAR(B) = YEAR(NOW())")

DATEDIFF

The DATEDIFF function calculates the difference between 2 dates (to get a positive number, enter the largest date first).

For example, to retrieve the list of users (C) and the registration date (B) of users registered less than 60 days ago, enter:

=QUERY(DB!A1:G15,"SELECT C, B WHERE DATEDIFF(NOW(), B) < 60")

LOWER

The LOWER function converts a text to lowercase.

To ignore case during a search and retrieve, for example, data from users whose first name (C) contains the letter "l" (in lowercase or uppercase), enter:

=QUERY(DB!A1:G15,"SELECT * WHERE LOWER(C) CONTAINS 'l'")

UPPER

The UPPER function converts a text to uppercase.

For example, to return the first 10 usernames (C) in uppercase, enter:

=QUERY(DB!A1:G15,"SELECT UPPER(C) LIMIT 10")