Replace all timestamps Dec 31, 1969

[datoshway]

Need a little help.  I have a date system where a date can be added or it can say TBA.  The problem is if there is no date in the field it's displaying dec 31st 1969. Whats the best way if there is the date is tba we just display tba and not the 1969 business.

heres my query

 

// get dates
$txtPageXML			.=	"\t<dates>\r\n";
$strQuery			= sprintf(	"SELECT d.strClinicDate FROM tblCitiesDates d LEFT JOIN tblCities c ON d.intCityID = c.intID WHERE c.intID = %d", intval($intCityID));
$queryGetDates		= db_query($strQuery);

if (db_num_rows($queryGetDates) > 0) {
	while ($objRow = db_fetch_object($queryGetDates)) {
		$strClinicDate		= stripslashes($objRow->strClinicDate);

		if (strlen($strClinicDate) > 0) {
			$strClinicDate		= date("M d, Y", strtotime($strClinicDate));
		}

		$txtPageXML			.=	"\t\t<date value=\"" . $strClinicDate. "\" />\r\n";
	}
}
db_free_result($queryGetDates);
$txtPageXML			.=	"\t</dates>\r\n";

[suresh_kamrushi]

Hey you can try this,

 

// get dates

$txtPageXML .= "\t<dates>\r\n";

$strQuery = sprintf( "SELECT IF( d.strClinicDate != NULL, d.strClinicDate, 0) as Cdate FROM tblCitiesDates d LEFT JOIN tblCities c ON d.intCityID = c.intID WHERE c.intID = %d", intval($intCityID));

$queryGetDates = db_query($strQuery);

if (db_num_rows($queryGetDates) > 0) {

while ($objRow = db_fetch_object($queryGetDates)) {

        $strClinicDate = stripslashes($objRow->Cdate);

        if (strlen($Cdate) != 0) {

        $strClinicDate = date("M d, Y", strtotime($strClinicDate));

        }

        $txtPageXML .= "\t\t<date value=\"" . $strClinicDate. "\" />\r\n";

        }

} db_free_result($queryGetDates);

$txtPageXML .= "\t</dates>\r\n";

 

 

I have updated select query and if statement.

 

 

[datoshway]

That didn't work, that put the date to 0 and got this error message.

 

Notice: Undefined variable: Cdate in index.php on line 150

[Pikachu2000]

How are the dates actually stored now? In what format, and what is the data type of the table field they're in?

[datoshway]

Hey thanks for the quick reply.  The dates are just varchar(255), they are formatted in the database like this: 2009-09-19 and the ones that are reading 1969 actually have TBA in the datafield which is what we want it to just out but it's giving me the 1969 instead

[Pikachu2000]

You should always use a field type that's designed to store dates. It allows you to use the many native date/time functions native to MySQL, such as formatting, comparison, conversion, etc., and it's much more efficient. If you can't make that change, I suppose you could do a comparison in php when echoing the values. If the only thing in the field is 'tba', you can check for it and not allow the date() formatting to occur. Just echo 'tba' instead.

 

This should get you pointed in the right direction, but seriously think about changing the say the dates are stored.

 

if (strlen($strClinicDate) > 0 && intval($strClinicDate) > 0 ) {
$strClinicDate = date("M d, Y", strtotime($strClinicDate));
} else {
$strClinicDate = 'TBA';
}

[datoshway]

You rock man.  Thank you so much.  That worked perfectly.  The only reason we aren't formatting the date field to actual date format is because of the variable of other items being stored like TBA in this case.  Anyway.  This worked really well for now, so thank you so much again for taking the time to help me out.

[Pikachu2000]

There is one caveat with the above since there are values other than 'tba' and dates in there. If the value isn't a date, but starts with a digit or series of digits with a value greater than 0, it will not echo the TBA, it will try to echo a date from whatever the string is . . . Let me know if that's the case.