datadoit.com
|
| Posted: 05/23/2005, 6:16 AM |
|
CCS 2.3; PHP4; MySQL 3.23.59
In the MySQL database, I have a field defined as DATE(10), default value
Null.
On a record form, I have a input control defined as Date, field format of
"mm/dd/yyyy", db format of "yyyy-mm-dd HH:nn:ss". For records where there's
a value in the database for the field, the data is displayed correctly. For
records where the date field value is null, it will always display
"12/01/2000".
I cannot seem to even test for a null value nor this 12/01/2000 date.
Where's this thing coming from and how do I get rid of it?
Gracias.
|
|
|
 |
DonB
|
| Posted: 05/24/2005, 6:51 AM |
|
I'm puzzled how you can realistically have 'DATE' with the field size of 10.
There are 8 digits in a date. With 10, that implies the 'hours', or so I
think. In fact, I can't even declare a DATE(10) in my 3.23.58 mySQL
database (Linux). It's not accepting the '10' for a CREATE TABLE. Howver,
I can go back and ALTER the column to a size of '10'.
Similar results for mySQL 4.1.1a
You might also try the date defined with no 'default value' (i.e., remove
the NULL default but leave the column as nullable). Conceptually, there is
no such thing as a 'null date' and I'm not sure what sideeffect you could
induce specifying a NULL. Alternatively, you might try specifying a zero or
'1900-01-01, etc. for the default to see if those give you a reliable
result. I wonder, too, if the default you have is a NULL or the string
'NULL' in the way the database interpretes it?
The value you get is 'suspicious' in that it conceivably is derived from
something like 0000-00-00 12:01:00 with the year being imputed as '2000' if
it's read as '00'. So I'm not ruling out the possibility of a
date-to-string conversion error, either. Which leads me back to my opening
statement - perhaps the string conversion is not working properly due to the
DATE field size.
--
DonB
http://www.gotodon.com/ccbth
"datadoit.com" <mike@datadoit.com> wrote in message
news:d6sl3n$l71$1@news.codecharge.com...
> CCS 2.3; PHP4; MySQL 3.23.59
>
> In the MySQL database, I have a field defined as DATE(10), default value
> Null.
>
> On a record form, I have a input control defined as Date, field format of
> "mm/dd/yyyy", db format of "yyyy-mm-dd HH:nn:ss". For records where
there's
> a value in the database for the field, the data is displayed correctly.
For
> records where the date field value is null, it will always display
> "12/01/2000".
>
> I cannot seem to even test for a null value nor this 12/01/2000 date.
>
> Where's this thing coming from and how do I get rid of it?
>
> Gracias.
>
>
|
|
|
|
datadoit.com
|
| Posted: 05/27/2005, 12:32 PM |
|
"DonB" <~ccbth~@gotodon.com> wrote in message
news:d6vbhe$k4l$1@news.codecharge.com...
> I'm puzzled how you can realistically have 'DATE' with the field size of
> 10.
> There are 8 digits in a date. With 10, that implies the 'hours', or so I
> think. In fact, I can't even declare a DATE(10) in my 3.23.58 mySQL
> database (Linux). It's not accepting the '10' for a CREATE TABLE.
> Howver,
> I can go back and ALTER the column to a size of '10'.
>
Using EMS Hi-Tech MySQL Manager, when you define a field as 'DATE', it
automatically specifies the field length of 10, which is not editable.
I suppose it thinks mm/dd/yyyy (10 digits)?
|
|
|
|
DonB
|
| Posted: 05/31/2005, 7:26 AM |
|
Perhaps. Can you go back and do an ALTER TABLE statement in your tool?
That's the only way I could select a different field size myself.
I'd try treating the field as simply a text value to see what it displays -
whether it's 2005-05-30 or 20050520. Different versions of PHP/mySQL will
give one of the other results in my experience. I really think this breaks
the date parsing (by extracting values from the wrong offsets in the string)
and puts the numbers for day into the year position or some other
combination like that one. Thus, the date you see formatted on-screen
looks screwy.
--
DonB
http://www.gotodon.com/ccbth
"datadoit.com" <mike@datadoit.com> wrote in message
news:d77skj$kp0$1@news.codecharge.com...
> "DonB" <~ccbth~@gotodon.com> wrote in message
>news:d6vbhe$k4l$1@news.codecharge.com...
> > I'm puzzled how you can realistically have 'DATE' with the field size of
> > 10.
> > There are 8 digits in a date. With 10, that implies the 'hours', or so
I
> > think. In fact, I can't even declare a DATE(10) in my 3.23.58 mySQL
> > database (Linux). It's not accepting the '10' for a CREATE TABLE.
> > Howver,
> > I can go back and ALTER the column to a size of '10'.
> >
>
> Using EMS Hi-Tech MySQL Manager, when you define a field as 'DATE', it
> automatically specifies the field length of 10, which is not editable.
>
> I suppose it thinks mm/dd/yyyy (10 digits)?
>
>
|
|
|
|
datadoit.com
|
| Posted: 06/03/2005, 6:31 AM |
|
Good info Don. Thanks.
However, I still have the problem of the field displaying the date of
12/01/2000 for fields that have no value. If a valid date is entered into
the field, all is well displaying that date afterwards. That tells me CCS
knows how to deal with the date if there's a value.
I have many many fields defined as DATE(10) in the MySQL database. It's
only this particular field that is displaying the 12/01/2000 value. If I
could trap that value somehow in the BeforeShow event that would be nice,
but I've been unable to do that using '0' or 'Null' or "". Looking at the
data directly in the database shows nothing in the field.
|
|
|
|
|
