January 2, 2017 at 6:43 am
Hi all,
Got a bunch of date data in the wrong column, I'd like copied into a column on the same table. Here's as far as I've got:
Update dbo.ReadingList
--SELECT convert (Datetime, RIGHT(Title, 9))
Set Date
SELECT RIGHT(Title, 10)
From ReadingList
WHERE ReadingList.Date is null
And ReadingList.Title not like 'Strong - Stronger.%'
And ReadingList.Title not like '%le force.'
Sample data: (sorry not sure how to enable grid view here
Title Date
'She wishes she hadn't 30/Mar/03' , 'NULL'
As you see the title ALSO contains the date.
But above code gives a syntax error, also I'm not sure if Update is even the right statement - anyone have the right code?
January 2, 2017 at 6:54 am
JaybeeSQL (1/2/2017)
Hi all,Got a bunch of date data in the wrong column, I'd like copied into a column on the same table. Here's as far as I've got:
Update dbo.ReadingList
--SELECT convert (Datetime, RIGHT(Title, 9))
Set Date
SELECT RIGHT(Title, 10)
From ReadingList
WHERE ReadingList.Date is null
And ReadingList.Title not like 'Strong - Stronger.%'
And ReadingList.Title not like '%le force.'
Sample data: (sorry not sure how to enable grid view here
Title Date
'She wishes she hadn't 30/Mar/03' , 'NULL'
As you see the title ALSO contains the date.
But above code gives a syntax error, also I'm not sure if Update is even the right statement - anyone have the right code?
Simple syntax errors, here is a correction
😎
Update RL
Set
RL.[Date] = RIGHT(RL.Title, 10)
From dbo.ReadingList RL
WHERE RL.[Date] is null
And RL.Title not like 'Strong - Stronger.%'
And RL.Title not like '%le force.';
January 2, 2017 at 7:02 am
You've only provided one line of sample data, so unsure this will work (I've guessed that the date is always at the end, and in the format dd/MMM/yy):
USE DevDB;
GO
CREATE TABLE #Sample (Title VARCHAR(100),
[Date] DATE);
INSERT INTO #Sample
VALUES ('She wishes she hadn''t 30/Mar/03' , NULL); --I assume this is a NULL, not a string with the value NULL (which are different).
GO
SELECT *
FROM #Sample;
GO
UPDATE #Sample
SET Title = RTRIM(LEFT(Title, LEN(Title) - 9)),
[Date] = RIGHT(Title,9)
WHERE [Date] IS NULL;
GO
SELECT *
FROM #Sample;
GO
DROP TABLE #Sample;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 2, 2017 at 7:06 am
You da man !!! 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply