CODEWITHSUNDEEP
phpmysqlcodeigniter-2expressionengine
ccdavies
ccdavies

Reputation: 1606

Where date is later than

I am storing a date as 'd-m-Y H:i:s'.

I need to check if this stored date is later than the $limit_date variable. The $limit_date variable is -30 days of the current date, stored in 'd-m-Y'.

I had thought the following would work, but no results are returned. 

$limit_date = strtotime('-30 day');
$limit_date = date("d-m-Y", $limit_date);

$this->db->where('date_submitted >', $limit_date);

My test date_submitted is '25-04-2017 09:13:13' with $limit_date returning '26-03-2017'.

Upvotes: 0

Views: 462

Answers (5)

Manish Patolia
Manish Patolia

Reputation: 517

i think you want to change date field string to date

STR_TO_DATE(date_submitted, '%Y-%m-%d')

$limit_date = date('Y-m-d', strtotime('-30 days'));

$this->db->where('STR_TO_DATE(date_submitted, '%Y-%m-%d') >', $limit_date);

Upvotes: 1

Sahil Gulati
Sahil Gulati

Reputation: 15141

In this code we are using DateTime and DateInterval. DateInterval for subtracting an exact amount of days you want to.

Try this code snippet here

<?php
ini_set('display_errors', 1);
$days=30;
$object = new DateTime("25-04-2017 09:13:13");//giving the date from which we want to subtract.
$object->sub(new DateInterval("P".$days."D"));//passing the interval which we want to subtract
echo $object->format("d-m-Y H:i:s");

Upvotes: 0

Honzy
Honzy

Reputation: 88

try: 

WHERE date_submitted > DATE_ADD(CURDATE(), INTERVAL - 30 DAY) // for 30days

or 

WHERE date_submitted > DATE_ADD(NOW(), INTERVAL - 30 DAY) // for 30*24hours

Upvotes: 0

GeorgeGeorgitsis
GeorgeGeorgitsis

Reputation: 1262

I think it would be better if you converted for the query the date_submitted in your DB to DATE if you don't want to mess with hours:minutes:seconds

Example: 

$limit_date = strtotime('-30 day');
$limit_date = date("d-m-Y", $limit_date);
$this->db->where('DATE(date_submitted) >', $limit_date);

Upvotes: 0

MANO
MANO

Reputation: 183

Try this:

$limit_date = strtotime('-30 day');
$limit_date = date("d-m-Y H:i:s", $limit_date);

$this->db->where('date_submitted >', $limit_date);

Upvotes: 1

Related Questions