DATETIME DEFAULT Now().

[Morgoth]

Hey,

I would like to know in what way, I can set the default value of a DATETIME field to Now() so I don't need to put the code in my scripts.

ALTER TABLE `tbltest` CHANGE `TheDate` `TheDate` DATETIME DEFAULT 'Now()'


With the ' ' it doesn't error, but it's probibly just sending the string "Now()" and I get the default date 0000-00-00.
If I take off the ' ' I get an error.

Anyone know a way I can set my default value for my DATETIME field to automatically insert the date and time with each new record?

[raf]

Hi Morgoth,

"A DEFAULT value has to be a constant, it cannot be a function or an expression. " dixit mysql.com
http://www.mysql.com/doc/en/CREATE_TABLE.html

So for datatime fields, you can use the now() function when you insert the records. Like

insert into testtable (text1, text2, datecreat) values ('ttttt','tttttt',NOW()).

And then it qill always register the system-datetime

[Morgoth]

raf,

But are you possitive that I can't have some sort of function to produce the date for me?

I guess that's one thing I will have to give up from access to MySQL, but some losses are expected when you get a better database service. ;)

[raf]

I'm quite positive about it. I checked by trying to crate a field where i esed it as default + checked the MySQL site and i found the quote from my previous post there. + in the link below, you'll see that they talk about using now() when you 'initialise' the value of a newly created row

But is easy to fix, you just need to use now() inside the insertstatement, like i posted.
Note: now() will be the sysdate of the MySQL server as compaired to now() in your server side code, which is the sysdate of the webserver (which could be different)

if you really need the db to take care of it, you could use a timestamp column, but this value is updated when the record is updated ... which is not always what you need.
http://www.mysql.com/doc/en/DATETIME.html


Access might be easier but this will be faster, since in access, the query first need addition processing to re-write it to include the sysdate as defaultvalue. (read query - look up defaultvalues for not include variables - rewrite query - execute query).

[Morgoth]

I guess I will just insert Now() Into my fields when I insert a new record.

No harm done, just something I would figure MySQL would, and should have. ;)

[Morgoth]

I am getting a problem with inserting the Now() function...


SQL = "INSERT INTO tbltest (Name, TheDate) VALUES ('Alex', '" & Now() & "')"


What is the problem? Well, it doesn't send the date and time, the date and timefield is left blank!

Do you know how to get this to work?
Preferably able to do it within one line inside the SQL string. (as how it looks now)

[raf]

Try

SQL = "INSERT INTO tbltest (Name, TheDate) VALUES ('Alex',Now())"


Less = more ;)

What you have now = trying to insert the ASP datetime, which is probably '5/07/2003 22:54:15', but for MySQL the datetimeformat needs to be 'yyyy-mm-dd hh:mm:ss'

If you use the statement above MySQL will insert the MySQL systemdatetime, which is in the right format. So it's actually easier then what you have.

If the MySQL runs on a different machine, and you need the systemdatetime of the webserver, then you first have t compose the dattime value in ASP, like

datetime = year(date) & "-" & month(date) & "-" & day(date) & " " & time

and then use

SQL = "INSERT INTO tbltest (Name, TheDate) VALUES ('Alex','" & datetime & "')"

[Morgoth]

Ok, well, the first line of code you gave me works, but it doesn't make any sence to me...

SQL = A string.
And you just put a function in the middle of a string.

If I go:
Response.Write "The Time Is: " & "Now()"
Then I will see:
The Time Is: Now()
Unless you are telling me, anytime I try to insert "Now()" as a string, MySQL searches my string, and if it finds "Now()" it will replace it with the system date in YYYY-mm-dd II:MM:SS format?

It's strang, but if what I said above is true, I guess I can live with it.

[raf]

All i say is true :D

But is is indead confusing. Let me try to explain. In ASP, everything between double-quotes (") is a stringvalue.
so
sql = "blblblb Now() respons.write(watever) year(date)" will be printed to the screen with
respons.write sql
as
blblblb Now() respons.write(watever) year(date)

Now, say we have a variabel (sql) that contains a sql-statement. (like "insert into table (date) values(Now())" ) When the webserver parses this, it treats it just like a string. The Now) isn't concidered as a fnction by the webserver. It's just the part of the string.

But when you execute the sqlstatement like
con.Execute sql
this statement is sent as a parametervalue (a MySQL command). The MySQL server (--> the RMDB) will proces this command and will recognize the Now() as a MySQL function. It's then that the systemdatetime will be inserted.


So there is a big difference between the ASP function Now() (--> which need to be included as a function and not as part of an ASP variabelvalue) and the MySQL function Now () (--> which needs to be included in the MySQL statement, which is a stringvalue of an ASP variabel).

Inside the sql-variabel, you need to use MySQL functions and follow the MySQL syntaxrules.

[Morgoth]

That's what I said..
Thanks for clarifing it!