Date problem

[lavanyajoomla]

Hi,

Iam trying to display the date which is greater than the current date.for that have written the code like below

$todaydate = date("d/m/y");

SELECT * FROM `jos_supplier_order` where due_date >"'. $todaydate.'";


It is giving the results like greater than the date only but it is not considering the month and year.if the d-- date is greater then it is displaying.It has to consider the month and the year also .

Is there any solution for this?


Thanks,

Lavanya.

[fastsol1]

Are you storing the due_date in the db in DATE format? If so then you need to change this

$todaydate = date("d/m/y"); 

To this

$todaydate = date("Y-m-d"); 

Since that is the format that the DATE column stores info in a mysql db. If not then show us the structure for the due_date in the db and an example of the date that is stored in the db.

[lavanyajoomla]

hi,

Thanks for the reply.

due_date data type is-------> varchar(255)

example date that is storing in the database is ------> 04/07/2011

below is the database table

CREATE TABLE `jos_supplier_order` (
`id` int(20) NOT NULL auto_increment,
`supplier_code` varchar(50) NOT NULL,
`order_number` int(25) NOT NULL,
`part_number` int(50) NOT NULL,
`due_date` varchar(255) NOT NULL,
`price` decimal(10,2) NOT NULL,
`processed` varchar(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ;


and the date is storing like

INSERT INTO `jos_supplier_order` (`id`, `supplier_code`, `order_number`, `part_number`, `due_date`, `price`, `processed`) VALUES
(1, '113', 1, 14246, '01/07/2011', 3.00, ''),
(2, '113', 1, 14253, '02/07/2011', 4.00, ''),
(3, '113', 1, 13918, '03/07/2011', 5.00, '');


and the using query is


$todaydate = date("d/m/y");

SELECT * FROM `jos_supplier_order` where due_date >"'. $todaydate.'";


It is giving the results like greater than the date only but it is not considering the month and year.if the d-- date is greater then it is displaying.It has to consider the month and the year also .

Thanks,

Lavanya

[fastsol1]

You need to change the due_date type in the db to DATE. varchar will not be able to do what you want cause php only sees the info as a string not a actual date. Change it to DATE format and change all your dates in the rows appropriately and you should be fine using the code I gave.

[JShor]

Actually, I don't recommend saving the type as DATE. I recommend using BIGINT and storing the values in UNIX time. It's a lot more flexible that way.