MySQL DateTime Format

sarahc

Hi,

Have inherited a web app, and had to convert the database from Access to MySQL. One remaining problem:
The following code inserts a new article into the database:
dbconn.execute("INSERT INTO News (news_title, news_content, date_posted) VALUES ('" & news_title & "', '" & news_content & "', '" & now() & "') ;")

The error message that is generated is:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E31)
[MySQL][ODBC 3.51 Driver][mysqld-5.0.18-nt]Incorrect datetime value: '13/03/2006 22:18:36' for column 'date_posted' at row 1

Can anyone help me to explicitly state the datetime format in the insert statement, please and thanks.

optiplexgx270

is your sql DB in american locale as this would cause the above error. Trying it insert the 13th month.

also check out...
http://www.comersus.org/forum/displayMessage.asp?mid=46382

Basq

Isn't the MySQL format for dates YYYY-MM-DD?

For example, the date above would be: 2006-03-13

Hmmmm... this any good to you

sarahc

thanks. but how do I explicitly insert the date in the correct format? i.e. the datetime is stored as YYYY-MM-DD HH:MM:SS in the datbase. But the now() is returning '13/03/2006 22:33:58'

I can't find the syntax to format the string as YYYY-MM-DD instead of DD/MM/YYYY

Basq

Not too sure.. try this:

("INSERT INTO News (news_title, news_content, date_posted) VALUES ('" & news_title & "', '" & news_content & "', '" & (now(), '%Y-%m-%d) & "') ;")

optiplexgx270

why not have a default value in the datetime col of now() and remove the now from the insert?

Basq

optiplexgx270 wrote:

why not have a default value in the datetime col of now() and remove the now from the insert?

I'd assume she wants a record of what time each one was posted (or more specifically inserted into the database) - so a default value would not suffice as depending on the number of records inserted, the time would change dramatically from the first record to the last record, and automatically entering the time yourself would be quite time-consuming not to mention monotonous.

sarahc

A default value of the current time would, of course, be ideal. But MySQL doesn't allow this - or do I understand it incorrectly. basquille is correct, the system chooses the records to display at the top of the list by the date. Although since there are very few records added we could get around the problem temporarily by altering them in the database.

Still, must be some way to do this...

optiplexgx270

i became aware of this about 30 sec after the post sorry...

optiplexgx270

date("y/m/d : H:i:s", Time())

http://www.experts-exchange.com/Web/Web_Languages/PHP/PHP_Databases/Q_20778597.html

rsynnott

Or, you know, just "INSERT INTO bla(a) VALUES(NOW())". It's there for a reason...

optiplexgx270

rsynnott wrote:

Or, you know, just "INSERT INTO bla(a) VALUES(NOW())". It's there for a reason...

thats what was attempted in the OP

rsynnott

optiplexgx270 wrote:

thats what was attempted in the OP

No, the OP attempted to use a 'now' function in whatever their disgusting, squalid client language is (it seems to belong to the "On Error Resume Next" class of languages, but beyond that I cannot say) which seems to return a localised date/time in a string. MySQL's NOW function returns the current time as a MySQL DATE or DATETIME type, depending on context.

sarahc

Thanks rsynnott - that works perfectly. Thanks to the others for assistance too - guess we all learned something

p.s. sqalid language - ho ho