change default value of column

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

try doing an ALTER TABLE|||I tried but it was not working|||

Quote:

Originally Posted by sourabhmca

I tried but it was not working

if this is a one time thing, try creating a new field then drop the existing one.|||oh yeah, you have to rename the field to the old field name after dropping the old one.

IF this is a one time thing...|||

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Hey try like this...

create table abc (a int,b smalldatetime constraint df_abc_b default getdate())
insert into abc(a) values(1)
select * from abc

alter table abc drop constraint df_abc_b
alter table abc add constraint df_abc_b default(getdate()+0.435) for b
insert into abc(a) values(1)
select * from abc

Cast Question - Converting Datetime into Date

Hi guys,
I knew that the Function Cast can do this but I tried a lot and I dont want to use the MONTH, YEAR, DAY function.

I have a smalldatetime field with a value of this 12/18/2004 4:02:00 PM
I just like to see it like this 12/18/2004

Hope you can help me up. Thanks.
-vinceSELECT CONVERT(varchar(10),GetDate(),101)

Got Books Online (BOL)?|||declare @.val datetime
set @.val='12/18/2005 11:00:00'
select @.val, convert(char(10),@.val,101)|||SELECT CONVERT(varchar(10),GetDate(),101)

Got Books Online (BOL)?

I have it here.. Thanks a lot...Sometimes, when you have a lot of things in your mind, you cant see exactly the answer which is sometimes in your face already. ;)

hehehe

Cast datetime to smalldatetime error

I have a table that I converted from MS Access and one of the colums I use t
o
store time values into has been converted to datetime and the year 1899 was
added to all existing records. I want to just display the time portion and
have tried the following code in the function I use to display the data:
CAST(ESR_CLOSE_TIME AS smalldatetime)
When I try to run the function I get an error saying the conversion resulted
in an overflow error. Any Ideas. Thanksyes - cast as datetime instead
the lowest smalldatetime value possible is Jan 1, 1900, so 1899 is out
of range.
AkAlan wrote:
> I have a table that I converted from MS Access and one of the colums I use
to
> store time values into has been converted to datetime and the year 1899 wa
s
> added to all existing records. I want to just display the time portion and
> have tried the following code in the function I use to display the data:
> CAST(ESR_CLOSE_TIME AS smalldatetime)
> When I try to run the function I get an error saying the conversion result
ed
> in an overflow error. Any Ideas. Thanks|||SELECT CONVERT(char(8), ESR_CLOSE_TIME , 108) --24hr time with seconds
SELECT RIGHT(CONVERT(char(19), ESR_CLOSE_TIME , 0),7) --with AM/ PM
The datetime datatype includes the date (year, month, day) and time with an
accuracy of 3/1000 of a second.
--
"AkAlan" wrote:

> I have a table that I converted from MS Access and one of the colums I use
to
> store time values into has been converted to datetime and the year 1899 wa
s
> added to all existing records. I want to just display the time portion and
> have tried the following code in the function I use to display the data:
> CAST(ESR_CLOSE_TIME AS smalldatetime)
> When I try to run the function I get an error saying the conversion result
ed
> in an overflow error. Any Ideas. Thanks