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

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.
GrapeladyAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
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"

reference

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
0
 
Steve WalesSenior Database AdministratorCommented:
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

0
 
GrapeladyAuthor Commented:
Thank you both for the solution and explanation, it is just what I needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.