convert nvarchar to smalldatetime

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

convert nvarchar to smalldatetime

Author

Topic

agismarkos
Starting Member

17 Posts

Posted - 2012-08-01 : 13:07:07

hello ,
I have a coloumn with nvarchar of the form '2009-01-06 04:30:00+01' this column has a 140.000 rows
i need to change it from nvarchar to small date time . I guess the easiest way is get rid of the +01 and then cast it
i have found many articles about around the net but cant put it alltogether.
Could someone tell me please how i could write the whole procedure that will cast this column ? Table has also 11 more colums.
thank you ,
Agis

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-01 : 13:12:37

declare @v nvarchar(20)
set @v = '2009-01-06 04:30:00+01'
select convert(smalldatetime, left(@v, 19))

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

agismarkos
Starting Member

17 Posts

Posted - 2012-08-01 : 19:04:57

quote:
Originally posted by tkizer

declare @v nvarchar(20)
set @v = '2009-01-06 04:30:00+01'
select convert(smalldatetime, left(@v, 19))

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

thanks a lot but how will i make this work for every different row?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-01 : 19:08:45

Please test in a test environment first.

update yourtable
set column1 = convert(smalldatetime, left(column1, 19))

Then you can alter the column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

agismarkos
Starting Member

17 Posts

Posted - 2012-08-03 : 08:14:59

quote:
Originally posted by tkizer

Please test in a test environment first.

update yourtable
set column1 = convert(smalldatetime, left(column1, 19))

Then you can alter the column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

that worked! thank you very much!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-03 : 13:20:02

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources