ALTER TABLE help 1

[welshone]

hello,
how can I alter a field in a table to have a default value ?

I am trying this :


ALTER TABLE tblchat
ADD thetime DEFAULT =time()


not working.

I am using Access2000, but wantto beable to do this via SQL, instead of using the gui,

thanks for any help.
Jamie

 

[jester777]

This is how I've done this with SQL 7. Hope it helps.


alter table tblchat add thetime smalldatetime default getdate()

 

[bperry]

There is no time() [yet!] in SQL Server. We have the getdate() function, which returns both the current date and current time.

We also have a datetime and smalldatetime datatypes, which hold both a date and time together in one field. You can't have just date or time; must be both. (Even if you only specify time, SQL will store a default date in there anyway.)

So, if you want the 'thetime' column to hold both date and time, then jester777's solution will work. I usually like to add the NULL/NOT NULL option, so that I'm always clear on whether a column is allowed to have nulls or not.

alter table tblChat
add thetime datetime
NOT NULL default getdate()

If you really don't need/want the date, you could store the time in a char or varchar column (instead of datetime), and then convert getdate() to just a string containing the time only.

alter table tblChat
add thetime varchar(16) NOT NULL
default convert(varchar(16),getdate(),114)

See BOL for the various conversion options. '114' is one optioon that return time only.

 

[tlbroadbent]

If you want straight Access answers post your question in an Access forum. I recommend forum701 "Microsoft: Access Queries and JET SQL." Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

tbroadbent:
I haven't worked in Access (well, I did a few times at gunpoint, but that's it), so I find I am unable to visualize what might be going on behind the original question. What's your guess of the possible scenario here?

 

[tlbroadbent]

Welshone posted, "I am using Access2000, but want to be able to do this via SQL, instead of using the gui,..."

I assume Welshone refers to Jet SQL and the Access GUI. Forum701 deals with Jet SQL. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

okay. I think I was thrown off by the suggestion to post in the Access forum. He's got something in Access, but wants to develop it/convert it/migrate it to SQL. I was just a little confused. Often happens.

 

[welshone]

sorry, I did mean access2000 not sql server.
but nevermind, I have decided to insert the value when a row is added instead of making a default value.

thanks for all the info. though.

 

[moonshadow]

I think you mean something like this:
ALTER TABLE dbo.ErrorLog ADD CONSTRAINT
DF_ErrorLog_ErrDateTime DEFAULT getdate() FOR ErrDateTime

where you already have a field, but just want to add a default value for it.