Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2014-07-10
Medium Priority
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 23

Assisted Solution

by:Steve Wales
Steve Wales earned 400 total points
ID: 40189361
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 49

Accepted Solution

PortletPaul earned 800 total points
ID: 40189426
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

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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

810 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