Results 1 to 5 of 5

Thread: Date problem

  1. #1
    Join Date
    Aug 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Date problem

    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.

  2. #2
    Join Date
    Jul 2010
    Location
    Minnesota
    Posts
    256
    Thanks
    1
    Thanked 21 Times in 21 Posts

    Default

    Are you storing the due_date in the db in DATE format? If so then you need to change this
    PHP Code:
    $todaydate date("d/m/y"); 
    To this
    PHP Code:
    $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.

  3. #3
    Join Date
    Aug 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Date storing problem

    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

  4. #4
    Join Date
    Jul 2010
    Location
    Minnesota
    Posts
    256
    Thanks
    1
    Thanked 21 Times in 21 Posts

    Default

    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.

  5. #5
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

    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.
    - Josh

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •