Inserting DateTime values not working properly 2005-05-12 - By Chris Robinson
Back
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,FinalTestHead
,PretestQMAX5_PLOSS,
QMAX_REG,QMAX5_REG,QMAX_PLOSS,QMAX5_PLOSS,UTDateTimeTested,FTDateTimeTested)
VALUES
(1,'G9B11111110505',123456,'ut909041533.txt','ft1001040703.txt',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=mysql@(protected)
Earn $52 per hosting referral at Lunarpages.
|
|
