Solved

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

Posted on 2013-11-08
6
790 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
  • 3
  • 2
6 Comments
 
LVL 82

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 82

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 82

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 42

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now