MySQL Date/Time week() Function

The week() is a MySQL date/time function. It is used to get the week portion from given date.

It returns a numerical value range from 1 to 53. In this article, we will elaborate the concept of WEEK() Function in detail by various examples.

Benefits of MySQL WEEK() Function:

• It is mostly used for grouping and analyzing data based on weekly intervals.

Syntax

Parameter:

Date_value : date for getting week

Mode : it is optional. It is used to specify what day the week starts on. It can be one of the following:

Returns:

This function returns the week portion of a date value.

Example 1

Explanation: While executing the above query, the week() function is used to return the week number for a given date.

Output: The following is the output of this example.

Example 2

Explanation: While executing the above query, the week() function used to return the week number for a given date.

Output: The following is the output of this example.

Example 3:

Explanation: While executing the above query, the WEEK function is used with the CURDATE() function to retrieve the week number of the year from the current date.

Output: The following is the output of this example.

Example 4:

Explanation: While executing the above query, WEEK function passes an empty string or a non-string value as an argument this function returns NULL.

Output: The following is the output of this example.

Example 5:

Explanation: While executing the above query, the week() function used to return the week number for a given date and time.

Output: The following is the output of this example.

Example 6:

Explanation: While executing the above query, WEEK function to get the week number for a date value with given mode value.

Output: The following is the output of this example.

Using the MySQL WEEK() function in a Table column

We can use the MySQL WEEK() function is used to return the week number for a given date in the table column.

Following the steps given below to implement the MySQL WEEK() function in a column of the table.

Step 1: First, create a table named Order_Details using CREATE TABLE statement.

Step 2: After that INSERT the data into a table using the INSERT TABLE statement.

Step 3: To check the information with the use of SELECT statement, whether the data is inserted or not.

Example:

Explanation: On execution of the above SELECT query, the WEEK() function used to returns the week number from the Order_Date column.

Output: The output of this example is given below.

Using MySQL WEEK() function in a Table with WHERE clause

We can use the MySQL WEEK() function to return the week number from a table column. By using WHERE clause we can get restricted data from the given column values.

Sample Table: Order_Details

Example:

Explanation: On execution of the above query, the WEEK() function is used to return the week value from the Order_Date column values where the value of the Amount column is greater than 550.

Output: The output of this example is given below.

Using the MySQL WEEK() function with GROUP BY CLAUSE

The MySQL WEEK() function is used in a table to return the week of the year from the table. It is used with the GROUP BY clause to return the rows for every group based on the criteria.

Sample Table: Order_Details

Example:

Explanation: On execution of the above statement, the WEEK function is used with GROUP BY CLAUSE() to count the group of orders in each week group in the Order_Date column.

Output: The output of this example is given below.

Using the MySQL WEEK() function with ORDER BY CLAUSE

By using the ORDER BY clause, we can specify the column order for each group based on the specified criteria.

Sample Table: Order_Details

Example:

Explanation: On execution of the above SELECT statement, the MySQL GROUP BY CLAUSE() function is used to count the number of orders on each week group and the ORDER BY CLAUSE is used to arrange the data in ascending order.

Output: The output of this example is given below.