February 14, 2018 at 8:57 am
I have a value that I need to change the first characters with the year and the last 2 characters with the month. The AttributeValue field value is 17WEBD01. I need to update the 17 with the current years last two digits and I need to update the 01 with the current month 2 digits.
I tried this statement and others but I keep getting errors
UPDATE [EC_StoreAttributes]
SET Left(AttributeValue, 2) = SELECT RIGHT(CONVERT(VARCHAR(8), @Today, 1), 2)
where AttributeType = 'ECSRCCODE' and StoreRecordId = '1'
February 14, 2018 at 9:27 am
I have tried with getdate please change it to the date you need and I have convereted into a string..
UPDATE [EC_StoreAttributes]
SET Left(AttributeValue, 2) = SELECT RIGHT(CONVERT(VARCHAR(10),GETDATE(),101),2)
, Right(AttributeValue, 2) = SELECT LEFT(CONVERT(VARCHAR(10),GETDATE(),101),2)
WHERE
AttributeType = 'ECSRCCODE' and StoreRecordId = '1'
Hope this should work!!
Happy coding
February 14, 2018 at 9:42 am
I'm getting an error message Incorrect syntax near the keyword 'Left'.
February 14, 2018 at 9:48 am
LEFT and RIGHT are just functions that return a string; you can't SET the LEFT/RIGHT characters of a string like this (it would be like trying to do SET GETDATE()=...).
There are a variety of combinations of string functions you could use to satisfy this; I'll just show one possibility:
DECLARE @test-2 TABLE (some_string VARCHAR(MAX));
INSERT INTO @test-2 VALUES ('17test01'),('17something05'),('16morerandomstuff11');
SELECT modified_string=STUFF(LEFT(CONVERT(VARCHAR,GETDATE(),12),4),3,0,SUBSTRING(some_string,3,LEN(some_string)-4))
FROM @test-2;
February 21, 2018 at 11:57 am
Can you help explain the script?
Can you help explain this part of the script.
SELECT modified_string=STUFF(LEFT(CONVERT(VARCHAR,GETDATE(),12),4),3,0,
This is the length that starts at position 3 then you subtract 4 characters to remove the first and last 2 characters of the value
SUBSTRING(some_string,3,LEN(some_string)-4))
February 21, 2018 at 1:36 pm
Sure!
The SUBSTRING part of the expression just returns the original string without the first and last 2 characters, as you noted.
Then the CONVERT turns the current datetime into a string using style 12, which is YYMMDD, as documented at https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles
Then the LEFT takes the leftmost 4 characters of that YYMMDD string, giving us a string that is today's date in YYMM format.
That is the string passed as the first parameter STUFF, which starts at position 3 (the second parameter, which means the insertion is after YY), replaces 0 characters (the third STUFF parameter, and with no replacement means it's pure insertion), and then inserts the result of the SUBSTRING (that is the 4th parameter to the STUFF).
In summary, the expression strips off the first 2 and last 2 characters of the original string, and then inserts that between the YY and MM in a YYMM version of today's date.
Hopefully that helps!
February 21, 2018 at 2:54 pm
UPDATE [EC_StoreAttributes]
SET AttributeValue = CONVERT(varchar(2), GETDATE(), 112) +
SUBSTRING(AttributeValue, 3, LEN(AttributeValue) - 4) +
CONVERT(varchar(2), GETDATE(), 3)
WHERE
AttributeType = 'ECSRCCODE' and StoreRecordId = '1'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 26, 2018 at 9:28 am
If I want the year and month I would need to add the right and left. correct
UPDATE [EC_StoreAttributes]
SET AttributeValue = RIGHT(CONVERT(VARCHAR(10), GETDATE(), 101), 2) +
SUBSTRING(AttributeValue, 3, LEN(AttributeValue) - 4) +
left(CONVERT(VARCHAR(10), GETDATE(), 101), 2)
WHERE
AttributeType = 'ECSRCCODE' and StoreRecordId = '1'
February 26, 2018 at 9:47 am
cfm - Monday, February 26, 2018 9:28 AMIf I want the year and month I would need to add the right and left. correctUPDATE [EC_StoreAttributes]
SET AttributeValue = RIGHT(CONVERT(VARCHAR(10), GETDATE(), 101), 2) +
SUBSTRING(AttributeValue, 3, LEN(AttributeValue) - 4) +
left(CONVERT(VARCHAR(10), GETDATE(), 101), 2)WHERE
AttributeType = 'ECSRCCODE' and StoreRecordId = '1'
There are actually two different approaches that could be used. You could start with the attribute and then add the year at the beginning and the month at the end or you could start with the year and month and insert the attribute in the middle.
This second approach may not be as obvious to native English speakers, because English doesn't regularly use infixes (which is essentially what this approach mirrors). The one case is called expletive insertion or f*ing insertion and is used to show emphasis. Examples include abso-<expletive>-lutely and in-<expletive>-credible where an expletive is inserted in the words "absolutely" and "incredible" respectively.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 26, 2018 at 12:29 pm
Oops, correction:
UPDATE [EC_StoreAttributes]
SET AttributeValue = CONVERT(varchar(2), GETDATE(), 12) -- 112)112) + /* 12 rather than 112 */
SUBSTRING(AttributeValue, 3, LEN(AttributeValue) - 4) +
CONVERT(varchar(2), GETDATE(), 3)
WHERE
AttributeType = 'ECSRCCODE' and StoreRecordId = '1'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 26, 2018 at 12:45 pm
cfm - Wednesday, February 14, 2018 8:57 AMI have a value that I need to change the first characters with the year and the last 2 characters with the month. The AttributeValue field value is 17WEBD01. I need to update the 17 with the current years last two digits and I need to update the 01 with the current month 2 digits.I tried this statement and others but I keep getting errors
   
UPDATE [EC_StoreAttributes]
    SET Left(AttributeValue, 2) = SELECT RIGHT(CONVERT(VARCHAR(8), @Today, 1), 2)
where AttributeType = 'ECSRCCODE' and StoreRecordId = '1' 
You are so wrong, in so, so many ways. In SQL we have a strongly typed language that includes a date data type. You just posted absolute garbage. A date representation in ANSI/ISO standard SQL is based on the ISO-8601 standards, whose display format (which has nothing to do with internal representation) is"yyyy-mm-dd", but it is not a character string.
This is like asking "on a scale from 1 to 10, what color is your favorite letter of the alphabet?"
Oh, and on top of that you don't know that a column is nothing whatsoever like a field! The idea of something named a "attribute_value" is also totally absurd because it mixes data and metadata in a data element name. .
Can you post DDL and explain exactly what you're trying to do in a consistent, rational, fashion that matches RDBMS?
Please post DDL and follow ANSI/ISO standards when asking for help.
February 26, 2018 at 3:22 pm
I would use CONVERT(CHAR(4), GETDATE(), 12) to get the YYMM characters, then STUFF the middle part of the existing attribute value into it. This avoids all those RIGHT() functions.
SET AttributeValue = STUFF(CONVERT(CHAR(4),GETDATE(),12), 3, 0, SUBSTRING(AttributeValue, 3, LEN(AttributeValue)-4))
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply