What is the field formula for finding the 1st of the month after 60 days of hire date?

Posted on 2014-07-10
Last Modified: 2014-07-11
What is the field formula for finding the 1st of the month after 60 days of hire date?

I have a formula for {Hire Date} + 60 to get the 60 days after hire date.
I need a formula to reflect what the 1st of the month would be after 60 days.
Please advise.
Question by:Grapelady
    LVL 22

    Assisted Solution

    by:Steve Wales
    Try this ?

    You could do it all in one line, I broke it down so you could see what's happening:

    declare @HireDate datetime,
            @HirePlus datetime,
            @FirstHirePlus datetime;
    select @HireDate = getdate();
    print @HireDate
    select @HirePlus = dateadd(dd, 60, @HireDate)
    print @HirePlus
    SELECT @FirstHirePlus = DATEADD(dd,-(DAY(DATEADD(mm,1,@HirePlus))-1),DATEADD(mm,0,@HirePlus)) 
    print @FirstHirePlus
    Outputs of prints:
    Jul 10 2014  4:54PM
    Sep  8 2014  4:54PM
    Sep  1 2014  4:54PM

    Open in new window

    LVL 47

    Accepted Solution

    In Crystal (I think it is) :

    DateAdd(“m”,1, DateSerial(year( date-of-hire+60 ),month( date-of-hire+60 ),1) )

    where you provide the "date-of-hire+60"


    basically once you have the date-of-hire+60 get that year and month, with 1 makes first of month of that date, then add one more month = next first of month after date-of-hire+60

    Author Closing Comment

    Thank you both for the solution and explanation, it is just what I needed.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now