Post reply

SQL Newbie Help

  • December 29, 2019 at 4:39 pm

    Go to Answer

    #3711009

    Why can I not create a new column and add 90 days to the date in column P_INDATE in MS SQL Server? I believe you can do this in MySQL. The P_INDATE has a data type of Date.

     

    Attachments:
    You must be logged in to view attached files.

  • December 29, 2019 at 4:49 pm

    Answer

    #3711011

    I figured it out.

    SELECT P_CODE, P_INDATE, DATEADD(DAY, 90, P_INDATE) AS EXPDATE
    FROM PRODUCT;

  • December 29, 2019 at 5:31 pm

    This was removed by the editor as SPAM

  • December 29, 2019 at 8:45 pm

    #3711027

    If you're absolutely positive that 90 days will always be the magic number, you could make a computed column that would auto-magically populate the column for you?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 30, 2019 at 12:08 am

    #3711053

    >> Why can I not create a new column and add 90 days to the date in column P_INDATE in MS SQL Server? <<

    Why don't you put this computation in a view or computed column that is not materialized? This is much safer and does not eat up physical storage. However, your rule might more likely be in terms of business days, in which case you will need to have a cat a calendar table somewhere in your schema.

    Also, in the future. Don't post pictures! We need DDL and sample code, not pictures. How often have you programmed from somebody drawing a picture with colored crayons?

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • December 30, 2019 at 5:07 am

    #3711090

    jcelko212 32090 wrote:

    >> Why can I not create a new column and add 90 days to the date in column P_INDATE in MS SQL Server? <<

    Why don't you put this computation in a view or computed column that is not materialized? This is much safer and does not eat up physical storage. However, your rule might more likely be in terms of business days, in which case you will need to have a cat a calendar table somewhere in your schema.

    Also, in the future. Don't post pictures! We need DDL and sample code, not pictures. How often have you programmed from somebody drawing a picture with colored crayons?

    A materialized/persisted computed column would be better because it would be indexable and  SARGable.

    But your comment got me thinking... I'm looking back at it and thinking that any kind of additional column is totally unnecessary.  It's just too easy to look back 90 days and still be SARGable.

    I totally agree with the potential about business days that you mention and that's what got me thinking about how bad any kind of additional column would actually be.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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