#1
  1. No Profile Picture
    Aji
    Banned
    SEO Chat Explorer (0 - 99 posts)

    Join Date
    Mar 2004
    Location
    Kolkata, India
    Posts
    18
    Rep Power
    0

    A php- mysql problem- date formats problem


    Hi all,

    I am having a small problem, I am entering the date to mysql database as date('F j, Y, g:i a') but in string format. The database field type for date is varchar(255).

    Now I want to search from a particular date(mm/dd/yyyy) to a particular date(mm/dd/yyyy), how will I do that?

    let me explain again

    Enter to mysql
    $enterdate=date('F j, Y, g:i a');
    insert into datetable(DateFLD, NameFLD) values ($enterdate,'$_POST[FirstName]');
    Here the field DateFLD is varchar(255)type.

    Problem
    Search between mm/dd/yyyy and mm/dd/yyyy

    Thanks
    Aji
  2. #2
  3. No Profile Picture
    Contributing User
    SEO Chat Discoverer (100 - 499 posts)

    Join Date
    May 2003
    Location
    India
    Posts
    354
    Rep Power
    17
    Hi !
    Why dont u store the epoch timestamp in the db ?? You could convert the timestamp to your required date format from PHP.. You would also not be able to make using of indexing in way you are storing the dates.
  4. #3
  5. No Profile Picture
    Registered User
    SEO Chat Explorer (0 - 99 posts)

    Join Date
    Apr 2004
    Location
    USA
    Posts
    9
    Rep Power
    0
    Originally Posted by Aji
    Hi all,

    I am having a small problem, I am entering the date to mysql database as date('F j, Y, g:i a') but in string format. The database field type for date is varchar(255).

    Now I want to search from a particular date(mm/dd/yyyy) to a particular date(mm/dd/yyyy), how will I do that?

    let me explain again

    Enter to mysql
    $enterdate=date('F j, Y, g:i a');
    insert into datetable(DateFLD, NameFLD) values ($enterdate,'$_POST[FirstName]');
    Here the field DateFLD is varchar(255)type.

    Problem
    Search between mm/dd/yyyy and mm/dd/yyyy

    Thanks
    Aji
    Why not store the date as a 'date' type?
    Would dramatically reduce the amount of coding ... MySQL could handle your request with a simple query

    http://dev.mysql.com/doc/mysql/en/Da...functions.html
  6. #4
  7. PHP & MySQL guru
    SEO Chat Adventurer (500 - 999 posts)

    Join Date
    Apr 2004
    Location
    UK
    Posts
    696
    Rep Power
    16

    Dates


    The thing about mySQL is that it stores dates as YYYY/MM/DD which I believe is the international convention (i.e. most significant figures first.)

    Using mktime() you can convert a string e.g "13/04/1980" to a timestamp. You can then store the timestamp which is great for querying.

    Or you can use the date type in mySQL. To code for this you need to do something like:

    $datetoenter = "13/04/1980";

    $date_nos = explode("/", $datetoenter);

    $formatted_date = $date_nos[2] . "-" . $date_nos[1] . "-" . $date_nos[0];

    $sql = "insert into foo (foo_date, foo_bar) values ('$formatted_date', $bar)"; etc etc



    Hope that helps. If not, PM me.
  8. #5
  9. No Profile Picture
    Registered User
    SEO Chat Explorer (0 - 99 posts)

    Join Date
    Apr 2004
    Location
    USA
    Posts
    9
    Rep Power
    0
    assuming you've switched to a date type:

    SELECT * FROM your_table
    WHERE date_field >= '2001-01-01'
    AND date_field <= '2002-01-01'

    returns rows from jan 1 2001 thru jan 1 2002.

Similar Threads

  1. Advanced search PHP and MySQL problem
    By bbratu in forum Web Design, Coding and Programming
    Replies: 3
    Last Post: Oct 26th, 2010, 07:28 PM
  2. Godaddy problem and poor customer support
    By tonyfelice in forum Google Optimization
    Replies: 46
    Last Post: Sep 19th, 2004, 03:17 AM
  3. Google API Problem - PHP
    By JRM in forum Web Design, Coding and Programming
    Replies: 4
    Last Post: Sep 30th, 2003, 10:22 AM

IMN logo majestic logo threadwatch logo seochat tools logo