Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Field that adds a number at the Beginning of the month

    Hi all this might be an odd question but i was wondering how would I have a field add automatically a month at the end of each month. for instance I have a field called TtlCountableMnths for each participant. This is to keep track how long a particiapnt has been on TANF. So if that field says 14 and the next month comes how do I get it say 15. Can anyone help pls??

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Build a view if you can, it is by far the best answer. Use a computed column if you must.
    Code:
    CREATE TABLE desiree (
       date_joined		DATETIME		NOT NULL
       )
    
    INSERT INTO desiree (date_joined)
       SELECT DATEADD(month, v.number, '1999-01-01')
          FROM master.dbo.spt_values AS v
          WHERE  'P' = v.[type]
             AND DATEADD(month, v.number, '1999-01-01') < GETDATE()
          ORDER BY v.number
    GO
    
    ALTER TABLE desiree
       ADD TANF_months AS DateDiff(month, date_joined, GetDate())
    GO
    
    SELECT * FROM desiree
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •