CODEWITHSUNDEEP
phpcodeigniterdate
beNerd
beNerd

Reputation: 3374

Storing date as text in database but need to find records between two dates?

I am storing dates as VARCHAR in mysql tables. I actually have a invoice table that stores invoice details along with its date. I store those dates with this function of php in VARCHAR column.

date("d/m/y")

Now i need to generate reports of sales like from 4/5/2012 to 4/5/2013. I am confused as to how to find the date range. Please guide.

I am using codeigniter.

Upvotes: 0

Views: 477

Answers (4)

chanjianyi
chanjianyi

Reputation: 615

i think you can get the date from char to timestamp , of course, you need to do some format for your date...

here is some sample...

$senttime = strtotime($date . ' ' . $hour . ':' . $min . ':00');
$exdate   = strtotime($exdate . ' 00:00:00');
$now      = strtotime("now");

if ($now < $senttime && $senttime < $exdate) {...

than you can get the range...

Upvotes: 0

Steward Godwin Jornsen
Steward Godwin Jornsen

Reputation: 1179

I'm providing a suggestion here rather than an explicit answer.

Do you have any specific reason for storing dates as varchar. Storing as integer at least makes more sense than varchar. Could you possibly rework your database? Store dates as dates yyyy-mm-dd. Or is that completely impossible at this time?

If you wanted the above format as display, you could 

<?php list($year, $month, $day) = explode("-", $date) ?>

and reorder for display as 

<?php echo $day."/".$month."/".$year ?>

If it is completely impossible to rework the db, then use MYSQL STR_TO_DATE

Please see if this link could be of help: http://www.w3resource.com/mysql/date-and-time-functions/mysql-str_to_date-function.php

[EDIT]

<?php
$date_from = '26/02/13';
$date_to = '26/02/13';

//Because STR_TO_DATE would create a valid date from what you have in varchar.
list($day, $month, $year) = explode("/", $date_from);
$date_from = $year . "-" . $month . "-" . $day;
list($day, $month, $year) = explode("/", $date_to);
$date_to = $year . "-" . $month . "-" . $day;

$this->db->where('STR_TO_DATE(`date`,"%d/%m/%y") >=', $date_from);
$this->db->where('STR_TO_DATE(`date`,"%d/%m/%y") <=', $date_to);
$query = $this->db->get('TABLENAME');

//use your result here
?>

Upvotes: 0

Wing Lian
Wing Lian

Reputation: 2418

Use MySQL's built in string to date parsing

SELECT * FROM your_table WHERE STR_TO_DATE(`date_column`,'%d/%m/%Y') BETWEEN '2012-04-05' to '2013-04-05'

or in CI ActiveRecord

$this->db->select('*')
$this->db->from('your_table');
$this->db->where('STR_TO_DATE(`date_column`,"%d/%m/%Y") >=', '2012-04-05');
$this->db->where('STR_TO_DATE(`date_column`,"%d/%m/%Y") <=', '2013-04-05');
$query = $this->db->get();
$results = $query->result();

Upvotes: 0

Marcel
Marcel

Reputation: 15722

If it's dates why not store as date? You could now add another column, then with a script or program populate this new column with the parsed and recomposed dates. Thus, your future queries can work with a date data type.

Upvotes: 1

Related Questions