Question 243811 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – creating a dynamic date range in php

Is there an elegant way to create two date variables in php or mySQL. Essentially I have a query that needs to be run covering 1st October to 30th September the following year, so right now it would be 1st October 2022 to 30th September 2023, obviously once into October the date range become 1st October 2023 to 30th September 2024 and so on each year. played getting the individual year/month/day with the date() function and munging two date variables from that conditional on the current year but its seems very clumsy and inelegant

Tags:

4

Answers


  1. 0

    This might be what you’re looking for

    $currentDate = new DateTime();
$currentYear = $currentDate->format('Y');

if ($currentDate->format('m') >= 10) {
    $nextYear = $currentYear + 1;
} else {
    $nextYear = $currentYear;
}

$startDate = new DateTime($nextYear . '-10-01');
$endDate = new DateTime(($nextYear + 1) . '-09-30');

echo 'Start Date: ' . $startDate->format('Y-m-d') . PHP_EOL;
echo 'End Date: ' . $endDate->format('Y-m-d') . PHP_EOL;
    Login or Signup to reply.
  2. 0

    You don’t need "Sept 30 and Oct 1", you need Oct 1 00:00:00 in successive years so you can check if dates are before or after that. Use DateTime and DateInterval for this, eg:

    $epoch = new DateTimeImmutable('2022-10-01');
$interval = new DateInterval('P1Y');

var_dump($epoch->format('c'));
$cur = $epoch;
for($i=0; $i<4; ++$i) {
    $cur = $cur->add($interval);
    var_dump($cur->format('c'));
}

    Output:

    string(25) "2022-10-01T00:00:00+02:00"
string(25) "2023-10-01T00:00:00+02:00"
string(25) "2024-10-01T00:00:00+02:00"
string(25) "2025-10-01T00:00:00+02:00"
string(25) "2026-10-01T00:00:00+02:00"
    Login or Signup to reply.
  3. 0

    MySQL:

    Use the DATE_FORMAT and DATE_ADD functions to create the date range from October 1st of the current year to September 30th

    SELECT
  DATE_FORMAT(NOW(), '%Y-10-01') AS start_date,
  DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 YEAR), '%Y-09-30') AS end_date;

    Use the start_date and end_date in a SQL query to filter rows in a table

    SELECT somestuff 
FROM sometable 
WHERE date_column >= DATE_FORMAT(NOW(), '%Y-10-01') 
  AND date_column <= DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 YEAR), '%Y-09-30');

    PHP: Demo – https://3v4l.org/Ch3rQ

    <?php

$currentDate = new DateTime(); // Current date
$startDate = new DateTime($currentDate->format('Y-10-01')); // October 1st of the current year
$endDate = new DateTime(($currentDate->format('Y')+1).'-09-30'); // September 30th of the following year

echo "Start Date: " . $startDate->format('Y-m-d') . "n";
echo "End Date: " . $endDate->format('Y-m-d') . "n";
    Login or Signup to reply.
  4. 0

    For a pure MySQL approach you could do:

    WHERE date_col >= DATE_FORMAT(CURRENT_DATE, '%Y-10-01') - INTERVAL IF(MONTH(CURRENT_DATE) < 10, 1, 0) YEAR
  AND date_col <  DATE_FORMAT(CURRENT_DATE, '%Y-10-01') + INTERVAL IF(MONTH(CURRENT_DATE) < 10, 0, 1) YEAR;

    And you can test with:

    SET @CURRENT_DATE = '2023-09-05';

SELECT
    DATE_FORMAT(@CURRENT_DATE, '%Y-10-01') - INTERVAL IF(MONTH(@CURRENT_DATE) < 10, 1, 0) YEAR AS start,
    DATE_FORMAT(@CURRENT_DATE, '%Y-10-01') + INTERVAL IF(MONTH(@CURRENT_DATE) < 10, 0, 1) YEAR AS end;

    Outputs:

    start end
    2022-10-01 2023-10-01

    Or:

    SET @CURRENT_DATE = '2023-10-01';

SELECT
    DATE_FORMAT(@CURRENT_DATE, '%Y-10-01') - INTERVAL IF(MONTH(@CURRENT_DATE) < 10, 1, 0) YEAR AS start,
    DATE_FORMAT(@CURRENT_DATE, '%Y-10-01') + INTERVAL IF(MONTH(@CURRENT_DATE) < 10, 0, 1) YEAR AS end;

    Outputs:

    start end
    2023-10-01 2024-10-01
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search