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

  )
donpickAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The only 100% safe format for a date is yyyymmdd, so try this:

DEFAULT ‘19900101'

If that does not work, then, as everyone else noted, we need the specific error msg you are getting.
0
 
Om PrakashCommented:
seems to be fine
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
0
 
Cenjoy100Commented:
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.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
mansooralia_yahooCommented:
your code is absolutely fine, just check with the foreign key constraints, for any issues.

what error are you getting
0
 
donpickAuthor Commented:
OK, from now on I will enter dates in YYYYMMDD format.  This works.
0
 
Scott PletcherSenior DBACommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.