Re: Inserting DateTime values not working properly - Chris Robinson - com.mysql.lists.win32

6 messages in com.mysql.lists.win32Re: Inserting DateTime values not wor...

From	Sent On	Attachments
Chris Robinson	12 May 2005 04:42
Ken Robinson	12 May 2005 06:59
Daniel da Veiga	12 May 2005 07:32
Jeremiah Gowdy	12 May 2005 07:35
Chris Robinson	12 May 2005 08:03
Petr Vileta	12 May 2005 14:23

Subject:	Re: Inserting DateTime values not working properly
From:	Chris Robinson (crob...@manchester.actaris.com)
Date:	05/12/2005 08:03:18 AM
List:	com.mysql.lists.win32

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.

Quoting Chris Robinson <crob...@manchester.actaris.com> (in part):

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".

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.