SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Thread: date format

  1. #1
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    date format

    If that date is stored in a database like this "2011-02-27 12:51:25"
    I want to search history, where history is written in the text field in this format
    "2011-02-27"
    I tried searching in this format "2011-02-27" did not retrieve any fields
    any help?

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT this FROM that WHERE DATE(column) = '2011-02-27'
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This method does not retrieve any data

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Are you storing the data as DATETIME ?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes,in this format "2011-02-27 12:51:25"

  6. #6
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Can you post the output of this command please?

    Code:
    SHOW CREATE TABLE table
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by altarek View Post
    ...history is written in the text field in this format
    "2011-02-27"
    are you ~sure~ you're using a DATETIME column?

    why did you say a text field, then?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
        $sqls ="select * from logs where timestamp between'$dst' and  '$dto' "
    this is syntax i used but not work

    i used textfield to insert the date on which I want to search for

  9. #9
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You're column datatype should be DATETIME.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  10. #10
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no it's timestamp

  11. #11
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by altarek View Post
    no it's timestamp
    So you are not storing it as DATETIME, as you previously said.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    is it TIMESTAMP or INTEGER ???

    some people use unix timestamps, which of course are integers, stored in INTEGER columns

    in any case, you can easily resolve this issue by letting us see the table -- please do the SHOW CREATE TABLE as previously requested
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •