modify nvarchar datatype to datatime datatype

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

General SQL Server Forums

New to SQL Server Programming

modify nvarchar datatype to datatime datatype

Author

Topic

tarz
Starting Member

31 Posts

Posted - 2008-03-14 : 13:08:57

Hi,

I imported a table from Accees to SQL 7 with data in it.
I need to modify one of the datatype columns to "datetime" from nvarchar.

I tried to convert it manually, in SQL Server Enterprise Manager tool, but it gave me an error.

I also tried, creating another column "DATE2-datatype:datetime" and updating the column with the old one.

UPDATE users SET DATE2 = DATE.. But it also faild,..

How can I modify the column?

Thank you.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-14 : 13:26:48

What was the error?

Most likely there is at least one value in there that won't convert to datetime.

If that is the case, you may be able to identify the culprit with:
select * from <table> where isDate(<nvarcharColumn>) = 0

isDate won't be 100 percent accurate but it can help find which values are giving you problems.

Be One with the Optimizer
TG

tarz
Starting Member

31 Posts

Posted - 2008-03-14 : 14:34:57

The error that is given me when I try to update the value:
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

sorry forgot to mention, some of the values are null

Thanks,

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-14 : 22:03:13

Tried with convert function?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-15 : 03:54:33

Always use proper DATETIME datatype to store data

Which format are the dates stored in nvarchar column?

declare @n nvarchar(20)
set @n='20081212'
select cast(@n as datetime)

Madhivanan

Failing to plan is Planning to fail

tarz
Starting Member

31 Posts

Posted - 2008-03-17 : 10:00:58

The cast worked.. Thanks..
Some values in nvarchar were incorrect. Had to change them manully..

How can I do a select statement to find specific format.. so If I get an invalid format date, I would set it to null.

Select Date from user where Date doest have 2007-10-12 or 2007-10-12 00:00:00.000 format..

Thank you!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-17 : 10:24:30

Try

where col like '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%' and ISDATE(col)=1

Madhivanan

Failing to plan is Planning to fail

tarz
Starting Member

31 Posts

Posted - 2008-03-17 : 10:54:28

Thanks, It works if I have
2007-10(day with two digits)-12(month with two digits)
and if I have
2007-1(day one digit)-6(month one digit) ..how would I do it?

Thanks for you help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-17 : 12:33:22

Try with ISDATE function

where LEN(col)>=8 AND ISDATE(col)=1

tarz
Starting Member

31 Posts

Posted - 2008-03-17 : 13:57:09

Thanks :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-18 : 02:49:19

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail