I have a table Members with column DayJoin of type varchar. I want to set default value of the column DayJoin should be the current day if user does not enter a value for that field.
In MSSQL Server I wrote this statement:
CREATE TABLE Members
(
ID int identity (1,1) primary key,
Email varchar(50),
Password Varchar(50),
Role Varchar(50) default 'Member',
DayJoin varchar(50) DEFAULT Convert(varchar, GETDATE(),103),
LastLogin varchar(50)DEFAULT Convert(varchar, GETDATE(),103),
FName varchar(50),
LName varchar(50),
Phone varchar(50),
DOB varchar(50),
Gender varchar(50),
IDcard varchar(50),
Address varchar(50),
City varchar(50),
Job varchar(50),
Avatar varchar(50) default 'images/noavatar.gif'
)
The statement gets the value of the current day and formats it as (dd/mm/yyyy). But when I run a similar one in mySQL, it's not working
CREATE TABLE `NewTable` (
`ID` int UNSIGNED NULL AUTO_INCREMENT ,
`Email` varchar(50) NULL ,
`Password` varchar(50) NULL DEFAULT '' ,
`Role` varchar(10) NULL ,
`DayJoin` varchar(10) NULL DEFAULT 'CURRENT_TIMESTAMP' ,
`LastLogin` varchar(10) NULL DEFAULT 'date_format(now(),%Y/%m/%d)' ,
`Firstname` varchar(50) NULL ,
`Lastname` varchar(50) NULL ,
`Phone` varchar(20) NULL ,
`DOB` varchar(10) NULL ,
`Gender` varchar(20) NULL ,
`IDcard` varchar(20) NULL ,
`Address` varchar(50) NULL ,
`City` varchar(50) NULL ,
`Job` varchar(50) NULL ,
`Avatar` varchar(50) NULL DEFAULT 'images/noavatar.gif' ,
PRIMARY KEY (`ID`)
)
ENGINE=ARCHIVE
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci
;
I get a response saying something like; "default value in error"
Does anyone know what the problem is?
remove the quotes around 'CURRENT_TIMESTAMP' in your code and use TIMESTAMP data type. It should read like:
`DayJoin` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
Use DATETIME type instead:
`DayJoin` DATETIME DEFAULT CURRENT_TIMESTAMP
It will give error as "Invalid default value for 'DayJoin' "
Use datetime or timestamp datatype for 'DayJoin'
`DayJoin` TimeStamp NULL DEFAULT 'CURRENT_TIMESTAMP' ,
Try this change your DayJoin datatype
`DayJoin` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
or
`DayJoin` TimeStamp NULL DEFAULT CURRENT_TIMESTAMP