w3resource

MySQL Date and Time Exercises: Get the distinct Mondays from hire_date in employees tables

MySQL Date Time: Exercise-3 with Solution

Write a MySQL query to get the distinct Mondays from hire_date in employees tables.

Sample table: employees


Code:

-- This SQL query retrieves distinct dates representing the first day of the week for each hire date in the 'employees' table.

SELECT DISTINCT( -- Selects distinct values of the result set.
    STR_TO_DATE( -- Converts a string into a date value.
CONCAT( -- Concatenates multiple strings into one string.
YEARWEEK(hire_date), -- Gets the year and week number for each hire date.
            '1' -- Appends '1' to the end of the concatenated string.
        ), 
        '%x%v%w' -- Specifies the format of the input string.
    )
) 
FROM 
employees; -- Specifies the 'employees' table.

Explanation:

  • The YEARWEEK() function returns the year and week number for a given date.
  • CONCAT() concatenates the year and week number with '1'.
  • %x%v%w represents the format for the input string: %x for the year, %v for the week (ISO 8601 week), and %w for the day of the week.
  • STR_TO_DATE() converts the concatenated string into a date value based on the specified format.
  • The SELECT DISTINCT() statement ensures that only unique dates are returned.
  • The query produces a distinct set of dates representing the first day of the week for each hire date in the 'employees' table.

Pictorial Presentation of the above query:

Pictorial: Query to get the distinct Mondays from hire_date in employees tables

Sample Output:

(STR_TO_DATE
 (CONCAT(YEARWEEK(hire_date),'1'),'%x%v%w'))
1987-06-08T04:00:00.000Z
1987-06-15T04:00:00.000Z
1987-06-22T04:00:00.000Z
1987-06-29T04:00:00.000Z
1987-07-06T04:00:00.000Z
1987-07-13T04:00:00.000Z
1987-07-20T04:00:00.000Z
1987-07-27T04:00:00.000Z
1987-08-03T04:00:00.000Z
1987-08-10T04:00:00.000Z
1987-08-17T04:00:00.000Z
1987-08-24T04:00:00.000Z
1987-08-31T04:00:00.000Z
1987-09-07T04:00:00.000Z
1987-09-14T04:00:00.000Z
1987-09-21T04:00:00.000Z

 

MySQL Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a MySQL query to display the last day of the month (in datetime format) three months before the current month.
Next:Write a MySQL query to get the first day of the current year.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/mysql-exercises/date-time-exercises/write-a-query-to-get-the-distinct-mondays-from-hire_date-in-employees-tables.php