Date returned as 00/00/0000 rather than blank

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sknelson
Forum Newbie
Posts: 5
Joined: Thu May 16, 2002 2:56 pm
Location: IA

Date returned as 00/00/0000 rather than blank

Post by sknelson »

I run a macro in a Microsoft Access database to convert the data to MySQL. If a date field is empty in Access, it remains empty in the "Insert Record" code that is created by the macro, but when I insert the text file into MySQL, the date is changed to 00/00/0000. When I query the data and post it to a PHP results page on my website, I would like the date field to be blank rather than return 00/00/0000. Could anyone help me out with this? I can find all kinds of help for formatting date fields, etc. but nothing about this problem.

Thanks much for any assistance!
User avatar
sam
Forum Contributor
Posts: 217
Joined: Thu Apr 18, 2002 11:11 pm
Location: Northern California
Contact:

Post by sam »

Because you are using a mysaql date field rather than an int or timestamp field there is no way to rid yourself of the 00/00/0000 format. I would suggest you looking using timestams to store your dates.

Cheers Sam
sknelson
Forum Newbie
Posts: 5
Joined: Thu May 16, 2002 2:56 pm
Location: IA

Different script solved the problem

Post by sknelson »

I downloaded ExportSQL script from http://www.cynergi.net/exportsql/ and it worked great! It puts "Null" in as the value rather than leaving it blank and that took care of my problem. Thanks for the help.
User avatar
sam
Forum Contributor
Posts: 217
Joined: Thu Apr 18, 2002 11:11 pm
Location: Northern California
Contact:

Post by sam »

no problem.

Cheers Sam
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

Post by Crashin »

When I want to display dates, but not the 0000-00-00 default that MySQL uses for NULL values, I use the following code to check the date and display nothing if appropriate:

Code: Select all

if ($date_variable != "0000-00-00") {
	list($year, $month, $day) = split('ї-./]', $date_variable);
	$date_variable = date('m/d/Y',mktime(0,0,0,$month,$day,$year));
}
else {
	$date_variable = "";
}
Enjoy, in case you still need it!
Post Reply