MySQL

In MySQL, date functions are used to manipulate and extract information from date and time values. These functions allow you to perform tasks like getting the current date, extracting parts of a date, adding or subtracting days, and comparing dates. Date functions are essential for handling time-based data, such as filtering records by date, calculating age, or determining the difference between two dates.

Here’s a detailed guide to some of the most commonly used MySQL date functions, with examples for new students:

1. CURDATE():

   • The CURDATE() function returns the current date in YYYY-MM-DD format.
   • Syntax:

   SELECT CURDATE() AS current_date;

   • This query returns the current date.

2. NOW():

   • The NOW() function returns the current date and time in YYYY-MM-DD HH:MM:SS format.
   • Example:

   SELECT NOW() AS current_datetime;

   • This query retrieves the current date and time.

3. DATE():

   • The DATE() function extracts the date part from a date or datetime value.
   • Example:

   SELECT DATE(NOW()) AS current_date_only;

   • This query extracts the date portion of the current date and time, omitting the time.

4. YEAR(), MONTH(), and DAY():

   • These functions extract the year, month, and day parts from a date.
   • Example:

   SELECT YEAR(hire_date) AS hire_year, MONTH(hire_date) AS hire_month, DAY(hire_date) AS hire_day 
   FROM employees;

   • This query retrieves the year, month, and day portions of the hire_date for each employee.

5. DATE_ADD():

   • The DATE_ADD() function adds a specific interval (such as days, months, or years) to a date.
   • Example:

   SELECT first_name, last_name, hire_date, DATE_ADD(hire_date, INTERVAL 1 YEAR) AS next_anniversary 
   FROM employees;

   • This query adds one year to the hire_date to calculate the next anniversary for each employee.

6. DATE_SUB():

   • The DATE_SUB() function subtracts a specific interval from a date.
   • Example:

   SELECT first_name, last_name, hire_date, DATE_SUB(hire_date, INTERVAL 1 MONTH) AS previous_month 
   FROM employees;

   • This query subtracts one month from the hire_date for each employee.

7. DATEDIFF():

   • The DATEDIFF() function returns the number of days between two dates.
   • Example:

   SELECT first_name, last_name, DATEDIFF(NOW(), hire_date) AS days_worked 
   FROM employees;

   • This query calculates the number of days each employee has worked since their hire_date.

8. DATE_FORMAT():

   • The DATE_FORMAT() function formats a date according to a specified format.
   • Example:

   SELECT first_name, last_name, DATE_FORMAT(hire_date, '%M %d, %Y') AS formatted_hire_date 
   FROM employees;

   • This query formats the hire_date as "Month Day, Year" (e.g., "January 01, 2023").

9. STR_TO_DATE():

   • The STR_TO_DATE() function converts a string into a date using the specified format.
   • Example:

   SELECT STR_TO_DATE('2023-09-18', '%Y-%m-%d') AS converted_date;

   • This query converts the string '2023-09-18' into a date.

10. LAST_DAY():

    • The LAST_DAY() function returns the last day of the month for a given date.
    • Example:

    SELECT LAST_DAY(NOW()) AS last_day_of_month;

    • This query retrieves the last day of the current month.

11. WEEKDAY():

    • The WEEKDAY() function returns the index of the weekday for a given date (Monday is 0, Sunday is 6).
    • Example:

    SELECT WEEKDAY(NOW()) AS current_weekday;

    • This query returns the index for the current weekday.

12. TO_DAYS() and FROM_DAYS():

    • The TO_DAYS() function returns the total number of days from the year 0 to the specified date, while FROM_DAYS() converts a number of days back into a date.
    • Example:

    SELECT TO_DAYS(NOW()) AS days_since_year_0;

Date functions in MySQL are powerful tools for handling and manipulating date and time values. They allow you to calculate intervals, format dates, and extract specific parts of date fields, making them essential for time-based operations in database queries.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT YEAR(order_date), MONTHNAME(order_date)
    count(*) as monthly_order_count
FROM act_order
GROUP BY YEAR(order_date), MONTHNAME(order_date);

SELECT HOUR(order_date)
    count(*) as order_count
FROM act_order
GROUP BY HOUR(order_date)
ORDER BY count(*) DESC
LIMIT 5;

SELECT YEAR(current_date()) - birth_year as age
    count(*) as customer_count
FROM org_client
WHERE gender= 'M'
GROUP BY YEAR(current_date()) - birth_year
ORDER BY YEAR(current_date()) - birth_year DESC
LIMIT 3;