March 3, 2007 at 1:00 pm
What is the best way to convert a table say
Int4, Int4, varchar 20, varchar-20
to
Int4, Int4, datetime, tinyint
where some values of the datetime and tinyint may be null.
Thanks
March 3, 2007 at 4:32 pm
USE SSC
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'Demo' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.Demo
GO
CREATE TABLE dbo.Demo
(
a INT NOT NULL,
B INT NOT NULL,
c VARCHAR(20) NULL,
d VARCHAR(20) NULL
)
GO
INSERT INTO dbo.Demo (a,b,c,d) SELECT 1,1,GetDate(),255
GO
SELECT CONVERT(DATETIME, c) AS c FROM dbo.Demo
SELECT CONVERT(TINYINT, d) AS d FROM dbo.Demo
--no errors in this exemple
GO
INSERT INTO dbo.Demo (a,b,c,d) SELECT 2,2,'invalid date',333
SELECT CONVERT(DATETIME, c) AS c FROM dbo.Demo
/*
Syntax error converting datetime from character string.
The statement has been terminated.
*/
GO
SELECT CONVERT(TINYINT, d) AS d FROM dbo.Demo
/*
The conversion of the varchar value '333' overflowed an INT1 column. Use a larger integer column.
The statement has been terminated.
*/
--Here you delete or update the bad data
GO
DELETE dbo.Demo WHERE a = 2
GO
--run the alter statements
ALTER TABLE dbo.Demo
ALTER COLUMN c DATETIME NULL
GO
ALTER TABLE dbo.Demo
ALTER COLUMN d TINYINT NULL
GO
SELECT * FROM dbo.Demo
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'Demo' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.Demo
GO
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply