SitePoint Sponsor

User Tag List

Results 1 to 18 of 18

Thread: Date?

  1. #1
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Date?

    OK, well I'm not sure what to do. In my table i have a column for date. I would like the date I insert to be in mm/dd/yyyy format. And I need to be able to sort records by date...oldest, newest, etc. How should I go about this? Should I use a special format in mysql? I know in php I can just use the date function and go from there. But I'm not sure if I should just set the type of the date column as varchar, or if I should use some special date format for it.

    Thanks for any help.

  2. #2
    SitePoint Member
    Join Date
    Dec 2003
    Location
    Coral Springs, FL
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    in the MySQL querey just use CURDATE() and that will insert the current date

  3. #3
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, thanks. What format though?

  4. #4
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I just tried it, and it inserted it like this

    2004-05-30 00:00:00

    Is there a way to format it? And would it matter if it was varchar or not when I try to sort it?

    Edit:


    Well i've just found out about the timestamp type.
    http://dev.mysql.com/doc/mysql/en/TI...P_pre-4.1.html

    I guess that could work well, with a timestamp 8. And then in my php i could somehow figure out how to edit it so it displays mm/dd/yyyy. But I'm not sure how since there's nothing to explode it with...

    Any comments?
    Last edited by Josh_; May 30, 2004 at 15:09.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    mysql, like all other databases, has a special internal format for storing date values

    (in some databases, it is an integer number representing the number of days since a base date like dec 31 1900 or something)

    so what you have to learn is the special syntax to get dates into the database (so that the database will recognize the specific value you intended) as well as the special syntax to get dates out of the database (so that you can display it the way you want)

    in mysql, you must enter dates in year-month-day sequence, but you can say '2004-05-30' (string) or 20040530 (number) or quite a few other variations

    the good news is, getting it out is drop dead simple

    just use either the DATE_FORMAT or TIME_FORMAT function

    so "I would like the date I insert to be in mm/dd/yyyy format" is a tiny bit off -- you cannot insert it that way, and it certainly isn't stored that way, but you can display it that way, or any other way that you wish
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks

    I got some help from somebody doing something similar that I want to be doing, and he said to use the NOW() function. I tried that, and the format is pretty much what I need. I can just use some string functions in php and manipulate it like I want.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, use DATE_FORMAT in the query, it's a lot easier, and makes for cleaner code too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm affraid I do not see date_format anywhere in phpmyadmin...Is it a function?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it is a mysql function

    13.5 Date and Time Functions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm...I still don't see it. Do I need a certain type specified for that column before I can see it?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you please show your query?

    Code:
    select '2004-05-31'
     , date_format('2004-05-31'
          , "%W, %M %d, %Y")
          
    2004-05-31     Monday, May 31, 2004
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm assuming you mean my table create SQL? If so, where do I find that...sorry, I'm new to this.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, i meant the query where you select the formatted date out of the database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well the thing is, I don't have a query. Right now I'm inserting stuff, but I don't know what format I should have the date column in. I want to eventually be able to select it and arrange it by date...but right now I want to get stuff inserted correctly.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, i understand

    as mentioned in post #5, you must enter dates in year-month-day sequence

    that gets the values in

    as far as the datatype is concerned, just declare it DATE

    you can later display it in any format you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, I see!

    So, I should keep the type of that field as varchar, and just enter it like this?
    2004-05-30

    and then when I need to select it, I use the date_format function?

    Thanks for the help!

    Edit:


    I just saw your edit, do I need to set any length for the date format?

    Edit:


    well I just tried it, and it enters it yyyy-mm-dd like you said it should. Should I set that column as NULL by default though?

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, use NULL for the default

    use DATETIME instead of DATE if you want each value to include a specific time component

    otherwise, don't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    Colonel Jack O'neill Josh_'s Avatar
    Join Date
    Apr 2004
    Location
    NC
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, gotcha. Thanks alot!


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
  •