kenjpete
asked on
MySQL Syntax Error with both CURDATE() and NOW()
I am converting a database from MS SQL Server to MySQL. I am trying to create my users table as follows:
CREATE TABLE TBL_users (
MemberID int NOT NULL AUTO_INCREMENT,
UserFirst nvarchar(50),
UserLast nvarchar(50),
UserAccess int DEFAULT 3,
PCode nvarchar(50),
UserEmail nvarchar(50),
SMS nvarchar(50),
Carrier nvarchar(50),
EmailType nvarchar(50) DEFAULT 'HTML',
Device nvarchar(50),
UserGroup nvarchar(50),
RegDate datetime NOT NULL DEFAULT CURDATE(),
isStrongPassword bit (0) NOT NULL DEFAULT,
isHashedPassword bit (0) NOT NULL DEFAULT,
PRIMARY KEY (MemberID)
);
I keep getting an error message that says I have a syntax error between the 'RegDate' line and the 'isHashedPassword' line? I have substituted CURDATE() for NOW() and get the same error. Also, I have tried the 'timestamp' data type for 'RegDate' but I don't want the 'RegDate' field to change when I do an update on a record in the database.
I have also changed the order of 'DEFAULT' and 'NOT NULL' in those lines but nothing seems to work??
Any ideas?
CREATE TABLE TBL_users (
MemberID int NOT NULL AUTO_INCREMENT,
UserFirst nvarchar(50),
UserLast nvarchar(50),
UserAccess int DEFAULT 3,
PCode nvarchar(50),
UserEmail nvarchar(50),
SMS nvarchar(50),
Carrier nvarchar(50),
EmailType nvarchar(50) DEFAULT 'HTML',
Device nvarchar(50),
UserGroup nvarchar(50),
RegDate datetime NOT NULL DEFAULT CURDATE(),
isStrongPassword bit (0) NOT NULL DEFAULT,
isHashedPassword bit (0) NOT NULL DEFAULT,
PRIMARY KEY (MemberID)
);
I keep getting an error message that says I have a syntax error between the 'RegDate' line and the 'isHashedPassword' line? I have substituted CURDATE() for NOW() and get the same error. Also, I have tried the 'timestamp' data type for 'RegDate' but I don't want the 'RegDate' field to change when I do an update on a record in the database.
I have also changed the order of 'DEFAULT' and 'NOT NULL' in those lines but nothing seems to work??
Any ideas?
Dave Baldwin
I believe that in MySQL, DEFAULT must be a single value and not a function. CURDATE() and NOW() can be used in an SQL Query but not as a default 'value'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok that worked! If I change the 'RegDate' to a data type of 'timestamp' and set the default value to 'CURRENT_TIMESTAMP' I don't get any errors (I also had to remove the 'DEFAULT' from 'isStrongPassword' and 'isHashedPassword'?).
However, what concerns me now is, if a user updates their account will the RegDate value change as a result of that change to a timestamp data type?
However, what concerns me now is, if a user updates their account will the RegDate value change as a result of that change to a timestamp data type?
I don't know for sure but I think the DEFAULT value is only used on an INSERT. Apparently, it only 'updates' if you tell it to or do an UPDATE with a NULL value for that column. See here: http://dev.mysql.com/doc/refman/5.1/en/timestamp-initialization.html
if a user updates their account will the RegDate value change as a result of that change to a timestamp data type?
Nope - it's the DEFAULT value that's set, so you won't have a problems. Give it whirl and see :)
And in your original SQL, you didn't set a DEFAULT value for the password fields - DEFAULT on it's own means nothing...
ASKER
Thanks Chris, I noticed that I omitted 'false' in those lines too!