varchar to date issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

varchar to date issue

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-10-03 : 11:25:50

Two of the columns I am selecting from a table hold dates but they are of type nvarchar(255)

Now I would like to transfer these two fields into a table with the fields in smalldatetime

This is the error I get:
Arithmetic overflow error converting expression to data type datetime.

How do I convert the nvarchar(255) to smalldatetime?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 11:32:23

Which format are they stored in?

SELECT CONVERT(SMALLDATETIME, Col1, x)

Look in Books Online for "CAST & CONVERT" to see which option x you can set.

E 12°55'05.25"
N 56°04'39.16"

Friznost
Starting Member

20 Posts

Posted - 2007-10-03 : 11:34:26

Declare @TestTbl table (DateField nvarchar(255))

Insert into @TestTbl
Select '03/14/2006' union all
Select '03/15/2006' union all
Select '03/16/2006'

Select Convert(Smalldatetime, DateField) as DateField
From @TestTbl

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-10-03 : 11:35:37

Solved by using 103
Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-03 : 11:36:46

please post some sample data.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 02:34:32

quote:
Originally posted by arkiboys

Two of the columns I am selecting from a table hold dates but they are of type nvarchar(255)

Now I would like to transfer these two fields into a table with the fields in smalldatetime

This is the error I get:
Arithmetic overflow error converting expression to data type datetime.

How do I convert the nvarchar(255) to smalldatetime?
Thanks

Always use proper DATETIME datatype to store dates

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources