Solved

MySQL Syntax Error with both CURDATE() and NOW()

Posted on 2013-11-08
6
833 Views
Last Modified: 2013-11-08
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?
0
Comment
Question by:kenjpete
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39634397
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'.
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 39634404
See here: http://dev.mysql.com/doc/refman/5.1/en/create-table.html
The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.
0
 

Author Comment

by:kenjpete
ID: 39634439
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?
0
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39634477
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
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39634486
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...
0
 

Author Comment

by:kenjpete
ID: 39634511
Thanks Chris, I noticed that I omitted 'false' in those lines too!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question