CODEWITHSUNDEEP
phpjavascript
Prabhu Murthi
Prabhu Murthi

Reputation: 2876

Date Issue: 30/11/-0001

I have a java script calendar control on my webpage for managing dates, in some time the date value is stored in my DB as "30/11/-0001".

It will be more appreciated if any one help me, please.....

Upvotes: 6

Views: 11657

Answers (5)

theking2
theking2

Reputation: 2870

The reason for this is probably a combination of two things:

  1. SQL server MariaDB and MySQL store unknown Dates as "0000-00-00 00:00:00".
  2. \DateTime::createFromFormat("Y-m-d H:i:s", "0000-00-00 00:00:00") creates a date value you mention in your question.

See here

So in the SQL world there are three types of values for DateTime and Date.

  1. a valid date
  2. NULL (provided null is allowed in the table design)
  3. 0000-00-00 00:00:00

In PHP you have null or a valid date. So the communication with the database has to decide what to do with case 2 and 3. You might want to change 0000-00-00... to null in PHP, but this will result in a NULL value when written back, which might not be allowed in the database design.

So long story short: make sure the queries in PHP match you decision. Create a function pair for the PHP <-> SQL paradigm switch for dates. As far as I know only dates are this complex.

(Btw. JavaScript has nothing to do with this)

Upvotes: 0

Capricorn
Capricorn

Reputation: 251

if (!empty($this->dob && $this->dob != '0000-00-00')) 
{
        $dob = date_format(date_create($this->dob), "d-m-Y");
} else {
        $dob = '';
}

Add this condition to resolve this date issue $this->dob != '0000-00-00'

Upvotes: 1

Pratik Butani
Pratik Butani

Reputation: 62419

After 2 days i got the solution using notes on php.net:

Note: If the number of the year is specified in a two digit format, the values between 00-69 are mapped to 2000-2069 and 70-99 to 1970-1999. See the notes below for possible differences on 32bit systems (possible dates might end on 2038-01-19 03:14:07).

Note: The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 UTC to Tue, 19 Jan 2038 03:14:07 UTC. (These are the dates that correspond to the minimum and maximum values for a 32-bit signed integer.) Prior to PHP 5.1.0, not all platforms support negative timestamps, therefore your date range may be limited to no earlier than the Unix epoch. This means that e.g. dates prior to Jan 1, 1970 will not work on Windows, some Linux distributions, and a few other operating systems. For 64-bit versions of PHP, the valid range of a timestamp is effectively infinite, as 64 bits can represent approximately 293 billion years in either direction.

because of this problem they are showing 01-01-1970 or 30-11--0001

So you may have to put Validation from where you taking inputs.

From My Side: I am taking Android side value so i have created Regular Expression for that.

You can test Regex here.

Hope it will helps you.

Upvotes: 2

Rudy Broersma
Rudy Broersma

Reputation: 227

The date you are trying to set is probably empty. I have the same issue with a Symfony2 project where DateTime's stored in SQL with a value of '0000-00-00 00:00:00' result in "30-11--0001" when parsed by Twig's date filter.

Upvotes: 6

Steve Nguyen
Steve Nguyen

Reputation: 5974

You need to do some testing on your front end and get the SQL statements that are constructed with different inputs and look at what's being inserted into the database. A malformed date in the SQL query may be the culprit for the db value you're seeing.

Upvotes: 1

Related Questions