Altering column type - 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 2005 Forums

Transact-SQL (2005)

Altering column type

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-18 : 08:45:33

How could I modify my script below to alter the column type to datetime after it has performed it's string manipulations please?


 


DECLARE @tbl varchar(1000), @col varchar(1000), @sql nvarchar(4000)

DECLARE Cur1 CURSOR FAST_FORWARD FOR
SELECT o.name tbl , c.name col

FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id
WHERE o.name = 'transactions' 
---- replace o.name = '....' with o.xtype='U' for ALL tables in database -------

AND lower(c.name) like '%date%'


OPEN Cur1
FETCH NEXT FROM Cur1 INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN

SET @sql = '
UPDATE dbo.' + @tbl + '
SET ' + QUOTENAME(@col) + ' = CASE
WHEN ' + QUOTENAME(@col) + ' = ''0'' THEN NULL
WHEN ISDATE(' + QUOTENAME(@col) + ') = 0 THEN NULL
WHEN ' + QUOTENAME(@col) + ' LIKE ''13%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''12%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''11%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''10%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''9%'' THEN ''199'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''8%'' THEN ''198'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''7%'' THEN ''197'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
ELSE ' + QUOTENAME(@col) + '
END

'

PRINT @sql
EXECUTE sp_executesql @sql --UNCOMMENT TO ACTUALLY RUN
FETCH NEXT FROM Cur1 INTO @tbl, @col
END
CLOSE Cur1
DEALLOCATE Cur1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 08:55:21

quote:

Originally posted by OldMySQLUser

How could I modify my script below to alter the column type to datetime after it has performed it's string manipulations please?


 


DECLARE @tbl varchar(1000), @col varchar(1000), @sql nvarchar(4000)

DECLARE Cur1 CURSOR FAST_FORWARD FOR
SELECT o.name tbl , c.name col

FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id
WHERE o.name = 'transactions' 
---- replace o.name = '....' with o.xtype='U' for ALL tables in database -------

AND lower(c.name) like '%date%'


OPEN Cur1
FETCH NEXT FROM Cur1 INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN

SET @sql = '
UPDATE dbo.' + @tbl + '
SET ' + QUOTENAME(@col) + ' = CAST(CASE
WHEN ' + QUOTENAME(@col) + ' = ''0'' THEN NULL
WHEN ISDATE(' + QUOTENAME(@col) + ') = 0 THEN NULL
WHEN ' + QUOTENAME(@col) + ' LIKE ''13%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''12%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''11%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''10%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''9%'' THEN ''199'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''8%'' THEN ''198'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
WHEN ' + QUOTENAME(@col) + ' LIKE ''7%'' THEN ''197'' + SUBSTRING(' + QUOTENAME(@col) + ', 2, LEN(' + QUOTENAME(@col) + '))
ELSE ' + QUOTENAME(@col) + '
END) AS datetime)

'

PRINT @sql
EXECUTE sp_executesql @sql --UNCOMMENT TO ACTUALLY RUN
FETCH NEXT FROM Cur1 INTO @tbl, @col
END
CLOSE Cur1
DEALLOCATE Cur1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-18 : 09:15:44

ISDATE is not reliable
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-19 : 06:06:43

quote:
Originally posted by madhivanan

ISDATE is not reliable
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

How about:


WHEN ISDATE(' + QUOTENAME(@col) + ') = 0 AND LEN(' QUOTENAME(@col) + ') + '= ''8'' + ' THEN NULL'

better?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 06:25:59

Thats should be ok as long as you are interested in any format of data that can be intrepreted as a date.
SELECT ISDATE('07/10-09')
SELECT ISDATE('11-11-09') etc.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 06:32:42

LEN BETWEEN 8 AND 10 depending on how the data is entered.
See post made 02/18/2008 : 05:47:53
here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97481

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-19 : 07:26:58

quote:
Originally posted by OldMySQLUser

quote:
Originally posted by madhivanan

ISDATE is not reliable
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

How about:

WHEN ISDATE(' + QUOTENAME(@col) + ') = 0 AND LEN(' QUOTENAME(@col) + ') + '= ''8'' + ' THEN NULL'

better?

It depends on which format the dates are stored in a varchar column

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources