Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-11-08
6
Medium Priority
?
867 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 84

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 84

Accepted Solution

by:
Dave Baldwin earned 2000 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 84

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 44

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

650 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