SQL Newbie Help

  • 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.
  • I figured it out.

    SELECT P_CODE, P_INDATE, DATEADD(DAY, 90, P_INDATE) AS EXPDATE
    FROM PRODUCT;
  • This was removed by the editor as SPAM

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

  • >> 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. 

  • 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