...

View Full Version : Resolved displaying the date/time as today, yesterday or 'date'?



LJackson
09-24-2008, 01:30 PM
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


$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";

Fumigator
09-24-2008, 03:40 PM
What error are you getting?

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

LJackson
09-24-2008, 03:54 PM
Hi Fumigator,

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

here is what i have:


$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

Fumigator
09-24-2008, 06:02 PM
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.

kbluhm
09-24-2008, 07:53 PM
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.

LJackson
09-24-2008, 08:02 PM
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


$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 :)

LJackson
09-24-2008, 08:06 PM
fumigator, i removed the DATE_FORMAT and replaced it with string and i was getting the following error


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 '( `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

kbluhm
09-24-2008, 08:10 PM
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.

LJackson
09-24-2008, 08:27 PM
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,


$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

kbluhm
09-24-2008, 08:43 PM
Where does formatedDate ever appear in any of the queries you have posted above?

LJackson
09-24-2008, 08:50 PM
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

kbluhm
09-24-2008, 08:58 PM
It's cleverly hidden right in the query... ;)


... AS dateString

LJackson
09-24-2008, 09:10 PM
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

kbluhm
09-24-2008, 09:21 PM
$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`.

LJackson
09-24-2008, 09:33 PM
thank you sooooooooo much lol that works a treat :D

cheers :thumbsup:

kbluhm
09-25-2008, 05:08 AM
Not a problem.

I would also suggest that if you have an auto-incrementing ID field, order by that as opposed to `datePosted`, as it is entirely possible to have multiple items posted at the same time... but the ID will always be unique.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum