Inserting DateTime values not working properly

Hi all,

I'm having a strange problem with an Insert into a DateTime field in
MySQL. I'm running v4.1.7 on Windows 2000 Server with Service pack 4.

Basically I'm running an insert query that inserts data into a couple of
DateTime fields (as well as some other fields). The query is (with sample
data)

INSERT INTO u6_filec_maindata_tbl
(FileUID,MeterNumber,UnitNumber,UTFilename,FTFilename,
UTNumber,UTQMAX_REG,UTQMAX5_REG,UTQMAX_PLOSS,UTQMAX5_PLOSS,F inalTestHead,PretestQMAX5_PLOSS,
QMAX_REG,QMAX5_REG,QMAX_PLOSS,QMAX5_PLOSS,UTDateTimeTested,F TDateTimeTested)
VALUES
(1,'G9B11111110505',123456,'ut909041533.txt','ft1001040703.t xt',11,0.150000,0.370000,10.800000,3.600000,
1242772,0.410000,-1.090000,-999.999000,1.150000,-999.999000, '20040909
153900','20040930 152200')

The query works fine & all the data inserts correctly.... except for the
values UTDateTimeTested ('20040909 153900') and FTDateTimeTested ('20040930
152200'). These fields are formatted as DateTime with default values of
"0000-00-00 00:00:00". After the insert, these two fields do not contain
the data in the Insert query. instead they contain the default value
"0000-00-00 00:00:00".

Now some background: These Inserts are carried out over ODBC by a program
I've written in C that imports text files full of result data into the
database. I've developed this program on Windows 2000 Professional, SP4
with the same MySQL version and ODBC driver. On this machine, the DateTime
data inserts correctly without any problems at all. I moved the program to
a Windows 2000 Server running SP4 (this is a fresh install, nothing else
was on the server). On this I installed MySQL and ODBC (also installed the
Query Browser and Administrator). I then copied the tables (the database
is MyISAM format) from the Windows 2000 Pro PC to the Windows 2000 Server
machine. I then ran the program and found that all the records using the
DateTime had this problem.

What I've tried to resolve the issue:
- Checking the regional settings on the server and 2k pro machines - both
are the same.
- Manually running the Insert query in the MySQL query browser. I get the
same result. It leaves the default values when I run it on the server, but
inserts the correct data when I run it on my Win2k pro machine.
- Doing a repair on the table.

Any ideas? I'm really stumped?

Thanks in advance,
Chris.


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Inserting DateTime values not working properly

Quoting Chris Robinson (in part):

> INSERT INTO u6_filec_maindata_tbl
> (FileUID,MeterNumber,UnitNumber,UTFilename,FTFilename,
> UTNumber,UTQMAX_REG,UTQMAX5_REG,UTQMAX_PLOSS,UTQMAX5_PLOSS,F inalTestHead,PretestQMAX5_PLOSS,
> QMAX_REG,QMAX5_REG,QMAX_PLOSS,QMAX5_PLOSS,UTDateTimeTested,F TDateTimeTested)
> VALUES
> (1,'G9B11111110505',123456,'ut909041533.txt','ft1001040703.t xt',11,0.150000,0.370000,10.800000,3.600000,
> 1242772,0.410000,-1.090000,-999.999000,1.150000,-999.999000, '20040909
> 153900','20040930 152200')
>
> The query works fine & all the data inserts correctly.... except for the
> values UTDateTimeTested ('20040909 153900') and FTDateTimeTested ('20040930
> 152200'). These fields are formatted as DateTime with default values of
> "0000-00-00 00:00:00". After the insert, these two fields do not contain
> the data in the Insert query. instead they contain the default value
> "0000-00-00 00:00:00".

Did you try inserting the data formatted like '2004-09-09 15:39:00' and
'2004-09-30 15:22:00'?

That's how I have formatted any dates when I use the datetime field.

Ken


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Inserting DateTime values not working properly

Ken is right, MySQL only accepts values formatted accourding to its
rules, check the manual for the exact syntax for inserting date and
time values or use Ken's format (that is correct for the type of input
you're doying).

Good luck,

On 5/12/05, Ken Robinson wrote:
> Quoting Chris Robinson (in part):
>=20
> > INSERT INTO u6_filec_maindata_tbl
> > (FileUID,MeterNumber,UnitNumber,UTFilename,FTFilename,
> > UTNumber,UTQMAX_REG,UTQMAX5_REG,UTQMAX_PLOSS,UTQMAX5_PLOSS,F inalTestHea=
d,PretestQMAX5_PLOSS,
> > QMAX_REG,QMAX5_REG,QMAX_PLOSS,QMAX5_PLOSS,UTDateTimeTested,F TDateTimeTe=
sted)
> > VALUES
> > (1,'G9B11111110505',123456,'ut909041533.txt','ft1001040703.t xt',11,0.15=
0000,0.370000,10.800000,3.600000,
> > 1242772,0.410000,-1.090000,-999.999000,1.150000,-999.999000, '20040909
> > 153900','20040930 152200')
> >
> > The query works fine & all the data inserts correctly.... except for th=
e
> > values UTDateTimeTested ('20040909 153900') and FTDateTimeTested ('2004=
0930
> > 152200'). These fields are formatted as DateTime with default values o=
f
> > "0000-00-00 00:00:00". After the insert, these two fields do not conta=
in
> > the data in the Insert query. instead they contain the default value
> > "0000-00-00 00:00:00".
>=20
> Did you try inserting the data formatted like '2004-09-09 15:39:00' and
> '2004-09-30 15:22:00'?
>=20
> That's how I have formatted any dates when I use the datetime field.
>=20
> Ken
>=20
>=20
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=3Ddanieldaveiga@gma=
il.com
>=20
>=20


--=20
Daniel da Veiga
Computer Operator - RS - Brazil

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: Inserting DateTime values not working properly

Or he could just remove the space. '20040909153900' is a perfectly valid
date/time.

----- Original Message -----
From: "Ken Robinson"
To:
Sent: Thursday, May 12, 2005 6:59 AM
Subject: Re: Inserting DateTime values not working properly


> Quoting Chris Robinson (in part):
>
>> INSERT INTO u6_filec_maindata_tbl
>> (FileUID,MeterNumber,UnitNumber,UTFilename,FTFilename,
>> UTNumber,UTQMAX_REG,UTQMAX5_REG,UTQMAX_PLOSS,UTQMAX5_PLOSS,F inalTestHead,PretestQMAX5_PLOSS,
>> QMAX_REG,QMAX5_REG,QMAX_PLOSS,QMAX5_PLOSS,UTDateTimeTested,F TDateTimeTested)
>> VALUES
>> (1,'G9B11111110505',123456,'ut909041533.txt','ft1001040703.t xt',11,0.150000,0.370000,10.800000,3.600000,
>> 1242772,0.410000,-1.090000,-999.999000,1.150000,-999.999000, '20040909
>> 153900','20040930 152200')
>>
>> The query works fine & all the data inserts correctly.... except for the
>> values UTDateTimeTested ('20040909 153900') and FTDateTimeTested
>> ('20040930
>> 152200'). These fields are formatted as DateTime with default values of
>> "0000-00-00 00:00:00". After the insert, these two fields do not contain
>> the data in the Insert query. instead they contain the default value
>> "0000-00-00 00:00:00".
>
> Did you try inserting the data formatted like '2004-09-09 15:39:00' and
> '2004-09-30 15:22:00'?
>
> That's how I have formatted any dates when I use the datetime field.
>
> Ken
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=jgowdy@cox.net
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Inserting DateTime values not working properly

Hi Guys,

Thanks for the replies. I think I was looking too hard for a solution, I
started reading up further on the datetime formats on the MySQL website and
tried a few things. Whilst I was testing things out I noticed that the
version on the development machine was 4.0.20, not 4.1.7! After trying out
removing the space between the date and time, it worked perfectly on the
win 2000 server. Sorry for wasting your time guys - thanks anyway.

Another thought though: How come it works on the older MySQL version (the
space between the data and the time that is?). Is there a way of getting
it to work in 4.1.7 or higher? It's just that If I have to remove the
space I'll have to trundle through a far amount of code (I've decided I'd
like to stick with 4.1 for the final version of the software, rather than
reverting back to 4.0).

Thanks again!
Best,
Chris.

At 15:36 12/05/2005, Jeremiah Gowdy wrote:
>Or he could just remove the space. '20040909153900' is a perfectly valid
>date/time.
>
>----- Original Message ----- From: "Ken Robinson"
>To:
>Sent: Thursday, May 12, 2005 6:59 AM
>Subject: Re: Inserting DateTime values not working properly
>
>
>>Quoting Chris Robinson (in part):
>>
>>>INSERT INTO u6_filec_maindata_tbl
>>>(FileUID,MeterNumber,UnitNumber,UTFilename,FTFilename,
>>>UTNumber,UTQMAX_REG,UTQMAX5_REG,UTQMAX_PLOSS,UTQMAX5_PLOS S,FinalTestHead,PretestQMAX5_PLOSS,
>>>QMAX_REG,QMAX5_REG,QMAX_PLOSS,QMAX5_PLOSS,UTDateTimeTeste d,FTDateTimeTested)
>>>VALUES
>>>(1,'G9B11111110505',123456,'ut909041533.txt','ft100104070 3.txt',11,0.150000,0.370000,10.800000,3.600000,
>>>1242772,0.410000,-1.090000,-999.999000,1.150000,-999.9990 00,'20040909
>>>153900','20040930 152200')
>>>
>>>The query works fine & all the data inserts correctly.... except for the
>>>values UTDateTimeTested ('20040909 153900') and FTDateTimeTested ('20040930
>>>152200'). These fields are formatted as DateTime with default values of
>>>"0000-00-00 00:00:00". After the insert, these two fields do not contain
>>>the data in the Insert query. instead they contain the default value
>>>"0000-00-00 00:00:00".
>>
>>Did you try inserting the data formatted like '2004-09-09 15:39:00' and
>>'2004-09-30 15:22:00'?
>>
>>That's how I have formatted any dates when I use the datetime field.
>>
>>Ken
>>
>>
>>--
>>MySQL Windows Mailing List
>>For list archives: http://lists.mysql.com/win32
>>To unsubscribe: http://lists.mysql.com/win32?unsub=jgowdy@cox.net
>>
>
>
>--
>MySQL Windows Mailing List
>For list archives: http://lists.mysql.com/win32
>To
>unsubscribe:
>http://lists.mysql.com/win32?unsub=crobinson@manchester.act aris.com
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Inserting DateTime values not working properly

Only two formats are universal for all MySQLversions
1) YYYYMMDDhhmmss as a string or number, all time without spaces
2) YYYY-MM-DD hh:mm:ss as a tring with space between date and time.
This two formats work from MySQL 3.x up to MySQL 5.x

Petr Vileta, Czech republic
(My server reject all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)



----- Original Message -----
From: "Chris Robinson"
To:
Sent: Thursday, May 12, 2005 5:03 PM
Subject: Re: Inserting DateTime values not working properly


> Hi Guys,
>
> Thanks for the replies. I think I was looking too hard for a solution, I
> started reading up further on the datetime formats on the MySQL website
and
> tried a few things. Whilst I was testing things out I noticed that the
> version on the development machine was 4.0.20, not 4.1.7! After trying
out
> removing the space between the date and time, it worked perfectly on the
> win 2000 server. Sorry for wasting your time guys - thanks anyway.
>
> Another thought though: How come it works on the older MySQL version (the
> space between the data and the time that is?). Is there a way of getting
> it to work in 4.1.7 or higher? It's just that If I have to remove the
> space I'll have to trundle through a far amount of code (I've decided I'd
> like to stick with 4.1 for the final version of the software, rather than
> reverting back to 4.0).
>
> Thanks again!
> Best,
> Chris.
>
> At 15:36 12/05/2005, Jeremiah Gowdy wrote:
> >Or he could just remove the space. '20040909153900' is a perfectly valid
> >date/time.
> >
> >----- Original Message ----- From: "Ken Robinson"
> >To:
> >Sent: Thursday, May 12, 2005 6:59 AM
> >Subject: Re: Inserting DateTime values not working properly
> >
> >
> >>Quoting Chris Robinson (in part):
> >>
> >>>INSERT INTO u6_filec_maindata_tbl
> >>>(FileUID,MeterNumber,UnitNumber,UTFilename,FTFilename,
>
>>>UTNumber,UTQMAX_REG,UTQMAX5_REG,UTQMAX_PLOSS,UTQMAX5_PLOS S,FinalTestHead,
PretestQMAX5_PLOSS,
>
>>>QMAX_REG,QMAX5_REG,QMAX_PLOSS,QMAX5_PLOSS,UTDateTimeTeste d,FTDateTimeTest
ed)
> >>>VALUES
>
>>>(1,'G9B11111110505',123456,'ut909041533.txt','ft100104070 3.txt',11,0.1500
00,0.370000,10.800000,3.600000,
> >>>1242772,0.410000,-1.090000,-999.999000,1.150000,-999.9990 00,'20040909
> >>>153900','20040930 152200')
> >>>
> >>>The query works fine & all the data inserts correctly.... except for
the
> >>>values UTDateTimeTested ('20040909 153900') and FTDateTimeTested
('20040930
> >>>152200'). These fields are formatted as DateTime with default values
of
> >>>"0000-00-00 00:00:00". After the insert, these two fields do not
contain
> >>>the data in the Insert query. instead they contain the default value
> >>>"0000-00-00 00:00:00".
> >>
> >>Did you try inserting the data formatted like '2004-09-09 15:39:00' and
> >>'2004-09-30 15:22:00'?
> >>
> >>That's how I have formatted any dates when I use the datetime field.
> >>
> >>Ken
> >>
> >>
> >>--
> >>MySQL Windows Mailing List
> >>For list archives: http://lists.mysql.com/win32
> >>To unsubscribe: http://lists.mysql.com/win32?unsub=jgowdy@cox.net
> >>
> >
> >
> >--
> >MySQL Windows Mailing List
> >For list archives: http://lists.mysql.com/win32
> >To
> >unsubscribe:
> >http://lists.mysql.com/win32?unsub=crobinson@manchester.act aris.com
> >
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=petr@practisoft.cz
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org