convert the datatype string to date

Question

Post reply

convert the datatype string to date

vasu.ssis

Default port

Points: 1423
More actions
August 19, 2008 at 10:31 pm

#196531

hi guys,
i am using sql server 2005 to convert datatype string to date for that i create one new column(datetime) in my table and i did
update tablename set newcolumn=oldcolumn
but it shows following error
Arithemetic overflow error converting to data type date time
the statement has been terminated
i am traying these for long 2 days
any help appricated

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

I would guess it is an issue with the format that the data is stored in, in the old column.

Is it always the same format e.g. mm/dd/yyyy? Can you post a sample of the data in the old column?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

vasu.ssis Default port Points: 1423 More actions · Answer 2

Hi john

it is my sample data of old column

primary key oldcolumn

----------- ---------

023456781 20050428

023456782 20040112

023456783 20061228

023456784 00000000

023456785 20050428

023456786 20060409

023456787 00000000

023456788 20060607

023456789 20049718

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I think the problem is those 00000000 values. They aren't convertable to datetime.

Try this

update tablename set newcolumn=CAST(oldcolumn AS DATETIME) WHERE ISDATE(oldcolumn)=1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John H Marsh SSChampion Points: 11671 More actions · Answer 4

Hello,

Yes, "GilaMonster" is right. It is the zero filled values that cause the problem.

There is the question of what you want to do in the case of zeros? Do zeros represent a non existent value for the row? In this case you might want the new column to contain a Null.

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

vasu.ssis Default port Points: 1423 More actions · Answer 5

its working fine

Thanks to GilaMonster and everyone who reply this qustion