NOW() dosn't work

[otuatail]

This taken from w3c schools fails

CREATE TABLE Orders
(
OrderId int NOT NULL,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT NOW(),
PRIMARY KEY (OrderId)
)

 

Error:

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW(),

PRIMARY KEY (OrderId)

)' at line 5

 

Can't understand this

Desmond.

[Mchl]

You can't use functions as default values for columns.

If you want such functionality for time use timestamp column

 

OrderDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

[otuatail]

Hi. The above database script was taken from the mysql.com website.

CURRENT_TIMESTAMP is time not date and time. The field is a datetime.

 

Desmond

 

[Mchl]

TIMESTAMP column stores both date and time, and thus CURRENT_TIMESTAMP sets both date and time. I use it everyday.

http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

 

As to the example you posted, perhaps you could show the source?

[otuatail]

Ok timestamp and datetime in mysql are interchangebale Yes/No

The following line in setting up the table dose not work

  `Date` datetime default NOW(),

 

Can someone twll me of a default statment that will AUTOMATICALY enter the current date AND time please

 

[Mchl]

Ok timestamp and datetime in mysql are interchangebale Yes/No

No. DATETIME can store dates from 1000-01-01 00:00:00 to 9999-12-31 23:59:59, TIMESTAMP can store dates from 1970-01-01 00:00:00 to 2038-01-19 03:14:07

 

Can someone twll me of a default statment that will AUTOMATICALY enter the current date AND time please

 

I already did. I also provided you with a link to relevant page in manual.

[otuatail]

Sorry but this does not work

DROP TABLE IF EXISTS `datetest`;
CREATE TABLE IF NOT EXISTS `datetest` (
  `ID` int(11) NOT NULL auto_increment,
  `dbServer` datetime default CURRENT_TIMESTAMP,
  `webServer` datetime default NULL,
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM PACK_KEYS=0;

Result:

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP,

  `webServer` datetime default NULL,

 

 

DROP TABLE IF EXISTS `datetest`;
CREATE TABLE IF NOT EXISTS `datetest` (
  `ID` int(11) NOT NULL auto_increment,
  `dbServer` TIMESTAMP default CURRENT_TIMESTAMP,
  `webServer` TIMESTAMP default NULL,
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM PACK_KEYS=0;

Result:

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP,

  `webServer` TIMESTAMP default NULL,

 

 

[Mchl]

CREATE TABLE IF NOT EXISTS `datetest` (
  `ID` int(11) NOT NULL auto_increment,
  `dbServer` TIMESTAMP default CURRENT_TIMESTAMP,
  `webServer` TIMESTAMP,
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM PACK_KEYS=0;

INSERT INTO datetest (webServer) VALUES (20100101123456);
SELECT * FROM datetest;

+----+---------------------+---------------------+
| ID | dbServer            | webServer           |
+----+---------------------+---------------------+
|  1 | 2010-06-27 23:39:27 | 2010-01-01 12:34:56 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

[otuatail]

No this does not work

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP,  `webServer` TIMESTAMP,  PRIMARY KEY  (`ID`)

 

Also NOW() works in microsoft SQL server and Oracle

[Mchl]

What mysql version you use?

SELECT @@version;

 

I know it works in MSSQL and Oracle. We're talking about MySQL here however, and it does not work in MySQL.

[otuatail]

can this be the problem?

MySQL 4.0.26

[Mchl]

It certainly can be. 4.x versions are no longer supported by MySQL itself. You basically should upgrade to 5.x as soon as you can.

 

You can assign CURRENT_TIMESTAMP value since MySQL 4.1.2

 

http://dev.mysql.com/doc/refman/4.1/en/timestamp.html

 

[otuatail]

Thanks for that. Unfortunatly I can't upgrade as the server is out of my hands. Also I was looking into using stored procedures. Is there a minimum version.

[salathe]

For stored procedures, you'll be needing MySQL 5.x

[luca200]

In MySQL 4.0, it's enough to declare a column as timestamp (as far as you have just one of those in your table).

It will be set to the current timestamp when you insert a row, just omit it in the INSERT statement.

Be aware that it will be updated when you update the row, unless you explicitly set it to its current value

[otuatail]

Thanks for all the help. I have managed to get MySql5 but I still need help with the insertion of the date and time.

DROP TABLE IF EXISTS `Monthly`;
CREATE TABLE IF NOT EXISTS `Monthly` (
  `MonthlyID` int(11) NOT NULL auto_increment,
  `Subject` varchar(255) default NULL,
  `Message` text,
  `Owner` tinyint,
  `DOE` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (`MonthlyID`)
) TYPE=MyISAM PACK_KEYS=0;


insert into Monthly VALUES ('','Subject','Message','1')

If the datetime is aouto inserted then this should work  ?

[Mchl]

No. If you want to omit some columns in insert, you need to specify which columns you're inserting.

 

INSERT INTO Monthly (Subject,Message,Owner) VALUES ('Subject','Message',1)

[otuatail]

Ok I understand this. In ANOTHER sql query I had an auto number id field and the query was

$sql = "INSERT INTO `EventLog` VALUES ('','" . $stamp . "','"  . $DateTime . "','" .$IP . "','" . $Page . "','" . $browser . "','" . $hit . "')";

I had to put  '', as the first to account for the auto ID.

 

in my example here I have a field that has to be auto inserted with the server time

`DOE` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

 

so how do I auto insert the time with or witout the extended sql VALUES()

 

Desmond.

 

 

 

[Mchl]

INSERT INTO Monthly (Subject,Message,Owner) VALUES ('Subject','Message',1)

[otuatail]

SORRY

Thanks for that I have been so used to inserting ('',

for unused fields. Thanks again.