SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: date

  1. #1
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    date

    Hey all...

    Im working on a news database/ aplication. When a user fills in a form with the approiate details like the headline and article text, the date that the article was entered into the database has to be recorded into the database as well.

    I then want to have the ability to pull the date out and have in in the format of

    March 12, 2001
    MONTH DD, YYYY

    And one last brainteaser, the dates have have the ability too be sorted by date. So the newer articles come before the older ones

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use the built in MySQL function DATE_FORMAT(field, mixed_format);

    PHP Code:
    $sql "SELECT DATE_FORMAT(datefield, '%M %e, %Y') as newdate from tablename"
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does it have to be stored a specific way on the database then??

    How can I test what date is newer If I do it that way

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can find out the format that the date must be stored in here:
    http://mysql.he.net/documentation/my....html#DATETIME




    And look at DATE_SUB() for checking if the date is newer than a specific date
    http://mysql.he.net/documentation/my...time_functions


    A word of caution, you must store the date in a date or datetime type field, in order to use any of the built-in MySQL date functions.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Zealot oodie's Avatar
    Join Date
    Jul 2000
    Location
    Misty Mountain
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can also sort them while selecting
    PHP Code:
    $sql "SELECT DATE_FORMAT(datefield, '%M %e, %Y') as newdate FROM tablename ORDER BY datefield"

  6. #6
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a further question to this problem. I get the date stored in a db ( as unix-timestamp ) like you said above:

    PHP Code:
    $sql "SELECT DATE_FORMAT(datefield, '%w,%M %e, %Y') as newdate from tablename"
    how can I change the information of the "w" (day of the week by number sunday=0 ...) to the german names of weekdays. I need something like this here:

    PHP Code:
    <?switch (datefield("w")) {
    case 
    0:echo "Sonntag";
    case 
    1:echo "Montag";
    case 
    2:echo "Dienstag";
    case 
    3:echo "Mittwoch";
    case 
    4:echo "Donnerstag";
    case 
    5:echo "Freitag";
    case 
    6:echo "Samstag";
    }
    but I don't know exactly how to combine it here?
    Thank you for helping, smorb

  7. #7
    SitePoint Zealot oodie's Avatar
    Join Date
    Jul 2000
    Location
    Misty Mountain
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I'm thinking is to set up another table to store the weekdays in Germany like this
    Code:
    Table day_name :
    ---------------
    id | name
    ---------------
    1  | Sonntag
    2  | Montag
    3  | Dienstag
    4  | Mittwoch
    5  | Donnerstag
    6  | Freitag
    7  | Samstag
    ---------------
    And the query would be like this:
    PHP Code:
    $sql "SELECT CONCAT(day_name.name,\", \",DATE_FORMAT(datefield,'%M %e %Y')) AS newdate FROM tablename,day_name WHERE day_name.id = DAYOFWEEK(datefield)"


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
  •