sql server mseq: I import text file into SQL table, one column
name 'date', datatype is varchar and string in field
like 'dd/mm/yy'. I want to convert varchar into
smalldatetime and output is 101 'mm/dd/yyyy'. my query is:

alter table name1 alter column date smalldatetime

error massage:Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data
type resulted in an out-of-range smalldatetime value.
The statement has been terminated.

then I try to convert.

alter table name1 add date1 smalldatetime
update name1
set date1=convert(varchar, date,101 )

error message:Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data
type resulted in an out-of-range smalldatetime value.
The statement has been terminated.

Thanks reply

you can make use of CONVERT function. See following example.

ex:
create table dt_Tab (dt varchar(12))

insert into dt_tab values ('31/12/75')
insert into dt_tab values ('31/01/75')
insert into dt_tab values ('28/02/75')

--add column
alter table dt_tab add date1 smalldatetime

--update statement with CONVERT function and using syntax format "3". See
more help on CONVERT function in books online.
update dt_tab
set date1 = convert (datetime,dt,3)

--
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com