How to convert nvarchar to datetime

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 2005 Forums

Transact-SQL (2005)

How to convert nvarchar to datetime

Author

Topic

ggouts
Starting Member

2 Posts

Posted - 2007-01-17 : 23:13:23

Hello all,

I'm totally new in SQL Server 2005 and yesterday I came across a table with a nvarchar field which contains some kind of datetime info like this:
'22/10/2004 19:34:03'
The '' signs are part of the value. Furthermore, the locale of this "date" is Greek (dd/mm/yyyy hh:mm:ss). I tried to change the field's data type but all went wrong.
This table has about 215,000 records.
Any chance changing the field's datatype?

Thanks a lot,

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-17 : 23:31:06

There are a few ways to accomplish this, the easiest way I know of is to add another column with the type being datetime

then do the following query

Update a
set a.newcolumndate = convert(datettime,a.Varchardate,103)
from table1 a

after that you can simply delete the old column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 01:46:18

First, I would have updated the column with

update mytable set mycolumn = replace(mycolumn, char(39), '')

Peter Larsson
Helsingborg, Sweden

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2007-01-18 : 03:09:06

what is the error message u gets when u change the datatype. run the following query to get whethere there is any invalid date data there in the table

select *from mytable where isdate(mycolumn)=0

Madhu

ggouts
Starting Member

2 Posts

Posted - 2007-01-18 : 20:25:42

The two update queries did the trick! All went good!

Thanks a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources