December 15, 2012 at 3:30 pm
I want to set the year in column name 1 to the year that is in column name 2.
For example,
update <tablename>
set year(ColName1) = year(ColName2)
where month(ColName1) = month(ColName2) and day(ColName1) = day(ColName2)
Can something like this work out?
"Nicholas"
December 15, 2012 at 4:30 pm
5280_Lifestyle (12/15/2012)
I want to set the year in column name 1 to the year that is in column name 2.For example,
update <tablename>
set year(ColName1) = year(ColName2)
where month(ColName1) = month(ColName2) and day(ColName1) = day(ColName2)
I'm assuming the both ColName1 and ColName2 are datetime (since you didn't specify).
You could do something like:
UPDATE tablename
SET ColName1 = CAST(CAST(MONTH(@ColName1) AS char(2)) + '/' + CAST(DAY(@ColName1) AS char(2)) + '/' + CAST(YEAR(@ColName2) AS char(4)) AS datetime)
WHERE MONTH(ColName1) = MONTH(ColName2) AND DAY(ColName1) = DAY(ColName2);
HTH,
Rob
December 15, 2012 at 4:47 pm
Yes, thank you for catching that. They are both of data type datetime.
"Nicholas"
December 15, 2012 at 7:49 pm
5280_Lifestyle (12/15/2012)
Yes, thank you for catching that. They are both of data type datetime.
Not sure if this is purely for the purposes of the example, but your query essentially sets column1 to column2, so the whole calculation is unnecessary.
That said - assuming you're just looking to understand the options of how to doing the calc, I would not use string operations on datetime data given the various collations out there and the issues that can cause.
I'd look at something more like:
update <tablename>
set year(ColName1) = dateadd(year,DATEDIFF(year,ColName1,ColName2),ColName1)
where month(ColName1) = month(ColName2) and day(ColName1) = day(ColName2)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 15, 2012 at 9:51 pm
robert.gerald.taylor (12/15/2012)
5280_Lifestyle (12/15/2012)
I want to set the year in column name 1 to the year that is in column name 2.For example,
update <tablename>
set year(ColName1) = year(ColName2)
where month(ColName1) = month(ColName2) and day(ColName1) = day(ColName2)
I'm assuming the both ColName1 and ColName2 are datetime (since you didn't specify).
You could do something like:
UPDATE tablenameSET ColName1 = CAST(CAST(MONTH(@ColName1) AS char(2)) + '/' + CAST(DAY(@ColName1) AS char(2)) + '/' + CAST(YEAR(@ColName2) AS char(4)) AS datetime)
WHERE MONTH(ColName1) = MONTH(ColName2) AND DAY(ColName1) = DAY(ColName2);
HTH,
Rob
Rob, that worked beautifully! Thanks!
"Nicholas"
December 16, 2012 at 5:49 pm
Here's another way that is a bit more terse:
DECLARE @T TABLE (ColName1 DATETIME, ColName2 DATETIME)
INSERT INTO @T
SELECT '2012-11-01', '2013-11-01'
UNION ALL SELECT '2012-11-01', '2013-11-02'
UPDATE @T
SET ColName1 = STUFF(ColName1, 8, 4, YEAR(ColName2))
WHERE MONTH(ColName1) = MONTH(ColName2) and DAY(ColName1) = DAY(ColName2)
SELECT * FROM @T
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply