[RESOLVED] date value issue

mmonaco

Take the following statement:

INSERT INTO timesheet SET `id`='', `resourceID`='b499beaa-3eb0-fc9e-8054-42ee5f194345', `taskID`='e2962df5-7517-eb26-fa1e-437e48540541', 
`billingDescription`='date won\'t work', `portalDescription`='', 
`consultantNotes`='', `startTime`='14:02:00', `endTime`='17:02:24', 
`date`='2005-19-12', `quantity`='3', `billRate`='100', `costRate`='20', 
`createdBy`='b499beaa-3eb0-fc9e-8054-42ee5f194345', 
`updatedBy`='b499beaa-3eb0-fc9e-8054-42ee5f194345', `updatedOn`='2005-12-19 17:02:39' ON DUPLICATE KEY UPDATE `id`='', 
`resourceID`='b499beaa-3eb0-fc9e-8054-42ee5f194345', `taskID`='e2962df5-7517-eb26-fa1e-437e48540541', `billingDescription`='date won\'t work', `portalDescription`='', `consultantNotes`='', `startTime`='14:02:00', 
`endTime`='17:02:24', `date`='2005-19-12', `quantity`='3', `billRate`='100', 
`costRate`='20', `createdBy`='b499beaa-3eb0-fc9e-8054-42ee5f194345', 
`updatedBy`='b499beaa-3eb0-fc9e-8054-42ee5f194345', `updatedOn`='2005-12-19 17:02:39'

No matter what the date is set to 0000-00-00.

When performing the statement from the command line the following error is thrown: ERROR 1292 (22007): Incorrect date value: '2005-19-12' for column 'date' at row 1

I really can't see what's wrong with my date value. Should I try to insert 20051912? I'm pretty sure those dashes are legal though.

bastien

what is the data type of that field?

mmonaco

The date field is a DATE datatype

bastien

updatedOn='curtime()'

mmonaco

Well, my issue is with the date column, not the updatedOn column. However I do have a question concerning the updatedOn... I have updatedOn TIMESTAMP so should the updatedOn be updated whenever the row is updated? Also, I have a createdOn column, can I set the createdOn column to be a DATETIME/TIMESTAMP which is by default set to the time when the row was inserted and then have the updatedOn column be updated each time the row is updated?

Installer

(Re your original question.) Following the hint given by "...incorrect date value..." and checking with the manual entry for date types it can be deduced that '2005-19-12' is...um...an incorrect date value (i.e. there is no 19th month).

mmonaco

Yeah, I just noticed this, I was converting between YYYY-MM-DD and MM/DD/YYYY and I had the days and months reversed, thank you.

bastien

AFAIK you can have multiple TIMESTAMP cols, but mysql will only automatically update one, the others must be set by sql code...so set the createdOn manually and auto-update the other