donpick
asked on
Create table - date variable, set DEFAULT
I have searched for an answer to this in the knowledgebase, cannot find an answer.
I am running SQL server 2000 on a server running Windows 2000 server.
My question:
I want to set a default date in a Create Table statement.
The default date must be Jan 1, 1990
Below is my code. Look at the field called Entrydate. SQL does not seem to like the code I wrote.
What is the correct way to assign a default date in a Create Table statement?
CREATE TABLE tblSecurity
(
Symbol varchar(7) PRIMARY KEY NONCLUSTERED NOT NULL DEFAULT 'UNK' ,
SymbolName varchar(50) NOT NULL DEFAULT '**UNKNOWN**' ,
SymbolDesc text ,
-- [Does this work? Can I enter data?]
SectorID bigint DEFAULT 1
FOREIGN KEY REFERENCES tblSector(SectorID) ,
-- 1 = Undefined
IndustryID bigint DEFAULT 210
FOREIGN KEY REFERENCES tblIndustry(IndustryID) ,
-- 210 = Undefined
MarketID varchar(4) DEFAULT 'Unk'
FOREIGN KEY REFERENCES tblMarket(MarketID) ,
GroupID varchar(4) DEFAULT 'Unk'
FOREIGN KEY REFERENCES tblGroupID(GroupID) ,
TypeID varchar(3) DEFAULT 'UNK'
FOREIGN KEY REFERENCES tblTypeID(TypeID) ,
EntryDate smalldatetime NOT NULL DEFAULT ‘1/1/1990’ ,
DateAdded smalldatetime NOT NULL DEFAULT GETDATE() ,
HasOptions smallint NOT NULL DEFAULT 0 ,
-- (get data from Telehart)
NoCalcFlag smallint ,
-- (this will be used at sometime, just not now)
UseVolume tinyint NOT NULL DEFAULT 1 ,
-- [1 = yes, 0 = no]
ActionFlag tinyint NOT NULL DEFAULT 1
)
I am running SQL server 2000 on a server running Windows 2000 server.
My question:
I want to set a default date in a Create Table statement.
The default date must be Jan 1, 1990
Below is my code. Look at the field called Entrydate. SQL does not seem to like the code I wrote.
What is the correct way to assign a default date in a Create Table statement?
CREATE TABLE tblSecurity
(
Symbol varchar(7) PRIMARY KEY NONCLUSTERED NOT NULL DEFAULT 'UNK' ,
SymbolName varchar(50) NOT NULL DEFAULT '**UNKNOWN**' ,
SymbolDesc text ,
-- [Does this work? Can I enter data?]
SectorID bigint DEFAULT 1
FOREIGN KEY REFERENCES tblSector(SectorID) ,
-- 1 = Undefined
IndustryID bigint DEFAULT 210
FOREIGN KEY REFERENCES tblIndustry(IndustryID) ,
-- 210 = Undefined
MarketID varchar(4) DEFAULT 'Unk'
FOREIGN KEY REFERENCES tblMarket(MarketID) ,
GroupID varchar(4) DEFAULT 'Unk'
FOREIGN KEY REFERENCES tblGroupID(GroupID) ,
TypeID varchar(3) DEFAULT 'UNK'
FOREIGN KEY REFERENCES tblTypeID(TypeID) ,
EntryDate smalldatetime NOT NULL DEFAULT ‘1/1/1990’ ,
DateAdded smalldatetime NOT NULL DEFAULT GETDATE() ,
HasOptions smallint NOT NULL DEFAULT 0 ,
-- (get data from Telehart)
NoCalcFlag smallint ,
-- (this will be used at sometime, just not now)
UseVolume tinyint NOT NULL DEFAULT 1 ,
-- [1 = yes, 0 = no]
ActionFlag tinyint NOT NULL DEFAULT 1
)
Can you please let me know the exact error getting after running this SQl
still you can try this,
EntryDate smalldatetime NOT NULL DEFAULT convert(smalldatetime ,'1/1/1990')
It should work.
Thanks.
still you can try this,
EntryDate smalldatetime NOT NULL DEFAULT convert(smalldatetime ,'1/1/1990')
It should work.
Thanks.
your code is absolutely fine, just check with the foreign key constraints, for any issues.
what error are you getting
what error are you getting
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, from now on I will enter dates in YYYYMMDD format. This works.
Sweet! Glad that worked.
To confirm, YYYYMMDD format is always correctly interpreted by SQL, regardless of language setting, date setting(s), or any other setting.
To confirm, YYYYMMDD format is always correctly interpreted by SQL, regardless of language setting, date setting(s), or any other setting.
EntryDate smalldatetime NOT NULL DEFAULT ('1/1/1990'),
when i copied the code. single quote was copied as " ‘ " and not single quote. may be this might be the issue