In SQL, how to calculate period between started_date and end

I would like to query count days between start_date and end_date with this script

SELECT 
    EXTRACT (DAY FROM (MAX(controldate) - MIN(controldate))) days
FROM 
    rpt_a1903_mon_bonus_log;

Do you have any better script? May you share it with me?

CodePudding user response：

You can use DATEDIFF in your query to fetch number of days in between the given date ranges.

Try this query,

SELECT DATEDIFF(max(controldate), min(controldate)) AS NumberofDays FROM rpt_a1903_mon_bonus_log;

CodePudding user response：

Something like this:

SELECT DATEDIFF(date1, date2) AS days FROM yourTableHere;

Use the DATEDIFF() function to retrieve the number of days between two dates in a MySQL database. This function takes two arguments:

The end date. (In your example, it’s the date2 column.) The start date. (In your example, it’s the date1 column.) These arguments can be date/datetime values, expressions that return date/datetime values, or columns of the datetime or date data type.

This function subtracts the start date from the end date and returns the number of days as an integer.