Update the first and last 2 charactors with year and month

  • cfm

    Old Hand

    Points: 336

    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’ 

  • sathwik.em91

    SSC-Addicted

    Points: 421

    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

  • cfm

    Old Hand

    Points: 336

    I’m getting an error message Incorrect syntax near the keyword ‘Left’.

  • Jacob Wilkins

    One Orange Chip

    Points: 27724

    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 TABLE (some_string VARCHAR(MAX));

    INSERT INTO @test 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;

  • cfm

    Old Hand

    Points: 336

    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))

  • Jacob Wilkins

    One Orange Chip

    Points: 27724

    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!

  • ScottPletcher

    SSC Guru

    Points: 97741


    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • cfm

    Old Hand

    Points: 336

    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’

  • drew.allen

    SSC Guru

    Points: 76387

    cfm - Monday, February 26, 2018 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'

    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


    How to post data/code on a forum to get the best help[/url].How to Post Performance Problems[/url]

  • ScottPletcher

    SSC Guru

    Points: 97741

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • jcelko212 32090

    SSCrazy Eights

    Points: 8755

    cfm - Wednesday, February 14, 2018 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' 

    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. 

  • Scott Coleman

    One Orange Chip

    Points: 27287

    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 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply