Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts

    Exclamation displaying the date/time as today, yesterday or 'date'?

    Hi,

    right i am trying to find out how to set the output of the date/time from my database to display today if the date = today or yesterday if the date = yesterdays date, or if other then it displays the date

    i also want the time to be displayed as 11.55 am for example

    now i did have some help on this before the database was reset to an older date which i am very greatful for but the code i was given wasnt working and i was told there was a simple typo in it which i cannot see

    here is the code can anyone spot it
    cheers

    PHP Code:
    $SQL "
        SELECT
            *,
            CASE datePosted
                WHEN DATE( `datePosted` ) = DATE( NOW() ) THEN 'today'
                WHEN DATE( `datePosted` ) = DATE( DATE_SUB( NOW(), INTERVAL 1 DAY ) ) THEN 'yesterday'
                ELSE
                DATE_FORMAT( `datePosted`, '%b %D, %Y' ) END AS dateString
                FROM `posts` WHERE `secID` = '{$secID[$i]}' ORDER BY `datePosted` DESC"

    Last edited by LJackson; 09-24-2008 at 09:34 PM.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    What error are you getting?

    (edit) I just noticed you need to use END CASE rather than just END. Sorry...

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    Hi Fumigator,

    i am currently getting an error with the end case piece of code

    here is what i have:

    PHP Code:
    $SQL "
        SELECT
            *,
            CASE 
                WHEN DATE( `datePosted` ) = DATE( NOW() ) THEN 'today'
                WHEN DATE( `datePosted` ) = DATE( DATE_SUB( NOW(), INTERVAL 1 DAY ) ) THEN 'yesterday'
                ELSE
                DATE_FORMAT( `datePosted`, '%b %D, %Y' ) END CASE AS dateString
                FROM `posts` WHERE `secID` = '{$secID[$i]}' ORDER BY `datePosted` DESC"

    the error i get is

    error 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE AS dateString FROM `posts` WHERE `secID` = 'secBO' ORDER BY `datePos' at line 7

    any ideas
    cheers

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Whoops I was wrong about "END CASE", it should be just "END".

    We'll figure this out eventually... try removing the date_format() and replacing with a simple string; maybe that's the problem.

  • #5
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    I cannot just call up a garage and tell them my car is making a funny noise and then expect them to diagnose the problem over the phone.

    Please post the error that you receive when running the original query. If you simply tell us "there is an error" then we cannot help you.

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    hi kbluhm, sorry i thought that because you were helping me before the database was set back 10 day that you were aware of the error i was having

    with the original code

    PHP Code:
    $SQL "
        SELECT
            *,
            CASE 
                WHEN DATE( `datePosted` ) = DATE( NOW() ) THEN 'today'
                WHEN DATE( `datePosted` ) = DATE( DATE_SUB( NOW(), INTERVAL 1 DAY ) ) THEN 'yesterday'
                ELSE
                DATE_FORMAT( `datePosted`, '%b %D, %Y' ) 
                END AS dateString
                FROM `posts` WHERE `secID` = '{$secID[$i]}' ORDER BY `datePosted` DESC"

    i get no date displayed at all, the forum loads up as it should but there is no date, no errors or date

    cheers for your help so far kbluhm its much appreciated

  • #7
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    fumigator, i removed the DATE_FORMAT and replaced it with string and i was getting the following error

    PHP Code:
    error 1You have an error in your SQL syntaxcheck the manual that corresponds to your MySQL server version for the right syntax to use near '( `datePosted`, '%b %D, %Y' ) END AS dateString FROM `posts` WHERE `' at line 7 
    thanks for spending your time helping me with this i appreciate it
    cheers

  • #8
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    Quote Originally Posted by LJackson View Post
    hi kbluhm, sorry i thought that because you were helping me before the database was set back 10 day that you were aware of the error i was having
    Sorry, but I don't recall you ever posting the error back then either.

    I plugged that query that I gave you yesterday into a table with a DATETIME field and it retrieves fine with no errors.
    Last edited by kbluhm; 09-24-2008 at 08:18 PM.

  • #9
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    oh well i Apologise if i didnt i honestly thought i did lol.

    ah thats strange my datePosted field is definatly datetime.

    just a thought when i set a varible to display the new date format how do i call it?

    at the moment i have,

    PHP Code:
                $result mysql_query($SQL)or die("error 1: " mysql_error());  ;
                
    $row mysql_fetch_array($result);
                
    $date_rep $row['formatedDate']; 
    i think the formatedDate is the problem, what would i replace that with???

    cheers

  • #10
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    Where does formatedDate ever appear in any of the queries you have posted above?

  • #11
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    umm it doesnt, its still left in from the old code ive spent all this time looking at the actual sql query that i forgot about the rest, oops that'll be why it aint working then?

    how would i call the new date?
    cheers

  • #12
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    It's cleverly hidden right in the query...
    Code:
    ... AS dateString

  • Users who have thanked kbluhm for this post:

    LJackson (09-24-2008)

  • #13
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    nice one mate your a legend it works,

    1 other question, it now says yesterday today etc but how do i get the time displayed at the mo there is no time, i would like the time displayed as 11.55 am or pm. would i need a new query, or can i use this one but add to it

    ive managed to get the time to display as i want on the 'other' dates but how do i go about adding it to the today, and yesterday fields

    thanks

    thanks again

  • #14
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    PHP Code:
    $SQL "
        SELECT
            *,
            CASE
                WHEN DATE( `datePosted` ) = DATE( NOW() )
                    THEN CONCAT( 'Today, ', DATE_FORMAT( `datePosted`, '%l:%i %p' ) )
                WHEN DATE( `datePosted` ) = DATE( DATE_SUB( NOW(), INTERVAL 1 DAY ) )
                    THEN CONCAT( 'Yesterday, ', DATE_FORMAT( `datePosted`, '%l:%i %p' ) )
                ELSE
                    DATE_FORMAT( `datePosted`, '%b %D, %Y, %l:%i %p' )
                END AS `dateString`
        FROM
            `posts`
        WHERE
            `secID` = '{$secID[$i]}'
        ORDER BY
            `datePosted` DESC
    "

    ...or you could just use the original query and format `datePosted` with PHP and append it to `dateString`.
    Last edited by kbluhm; 09-24-2008 at 09:28 PM.

  • Users who have thanked kbluhm for this post:

    LJackson (09-24-2008)

  • #15
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    thank you sooooooooo much lol that works a treat

    cheers


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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