April 23, 2010 at 5:20 am
Hello everyone
Here is a table
ID date('varchar' type)
1 20100422
2 20100423
3 20100432 (this date can't be converted to the "date" type)
And i want to have a well-formatted table with converted dates and make the nonconvertible date to be assigned to the value of null
ID date('date' type)
1 2010-04-22
2 2010-04-23
3 null
How can I achieve this without using cursor then? Like in one update script?
Thanks for you opinions!
Bazinga!
April 23, 2010 at 6:15 am
Take a look at the ISDATE (Transact-SQL) function.
I can't think of a way to change the data type of the column at the same time if values exist that cannot be implicitly converted to the new type. You would have to set the 'invalid' values to NULL first and then alter the column type.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 23, 2010 at 6:32 am
This should hopefully do the trick
--Create Our Varchar Date Test Table
IF OBJECT_ID(N'Tempdb..#D', N'U') IS NOT NULL
DROP TABLE #D
GO
CREATE TABLE #D
(
IDINT IDENTITY(1,1),
DateVARCHAR(8)
PRIMARY KEY CLUSTERED (ID)
);
INSERT INTO #D (Date)
SELECT '20100422' UNION ALL --valid date
SELECT '20100423' UNION ALL --valid date
SELECT '20100432'; --invalid date
--Check for Invalid varchar date formats
SELECT ID, Date, ISDATE(Date) FROM #D;
--Set these to NULL
UPDATE #D
SET Date =
CASE
WHEN ISDATE(Date) = 0 THEN NULL
ELSE Date
END;
--See if remaining values convert to datetime ok
SELECT
ID,
Date,
CAST(Date AS DATETIME)
FROM #D;
--Change Column to Datetime
ALTER TABLE #d
ALTER COLUMN Date DATETIME
SELECT ID, Date FROM #D
April 23, 2010 at 6:41 am
Are you looking for something like this:
CREATE TABLE dbo.x
(nodatetime NVARCHAR(50));
INSERT INTO dbo.x
(x.nodatetime)
VALUES ('5/5/05')
,('4/4/04')
,('3/3/zz');
UPDATE dbo.x
SET x.nodatetime = NULL
WHERE ISDATE(x.nodatetime) = 0;
SELECT * FROM dbo.X;
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2010 at 6:42 am
Dohsan (4/23/2010)
This should hopefully do the trick
--Create Our Varchar Date Test Table
IF OBJECT_ID(N'Tempdb..#D', N'U') IS NOT NULL
DROP TABLE #D
GO
CREATE TABLE #D
(
IDINT IDENTITY(1,1),
DateVARCHAR(8)
PRIMARY KEY CLUSTERED (ID)
);
INSERT INTO #D (Date)
SELECT '20100422' UNION ALL --valid date
SELECT '20100423' UNION ALL --valid date
SELECT '20100432'; --invalid date
--Check for Invalid varchar date formats
SELECT ID, Date, ISDATE(Date) FROM #D;
--Set these to NULL
UPDATE #D
SET Date =
CASE
WHEN ISDATE(Date) = 0 THEN NULL
ELSE Date
END;
--See if remaining values convert to datetime ok
SELECT
ID,
Date,
CAST(Date AS DATETIME)
FROM #D;
--Change Column to Datetime
ALTER TABLE #d
ALTER COLUMN Date DATETIME
SELECT ID, Date FROM #D
Ah, I was slow. I was typing it up & testing it while you were posting. Well done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2010 at 6:45 am
Thanks very much folks for your warm-heartedness!
Bazinga!
April 23, 2010 at 7:03 am
Grant Fritchey (4/23/2010)
INSERT INTO dbo.x (x.nodatetime)
I never knew you could specify a table alias (x) in the INSERT column list...how interesting.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 23, 2010 at 7:15 am
Paul White NZ (4/23/2010)
Grant Fritchey (4/23/2010)
INSERT INTO dbo.x (x.nodatetime)I never knew you could specify a table alias (x) in the INSERT column list...how interesting.
Yeah, I didn't either. It was a typo that worked.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply