Solved

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

Posted on 2013-11-08
6
801 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

777 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