Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

get the number of months between start and end dates

Posted on 2014-03-20
7
Medium Priority
?
225 Views
Last Modified: 2014-03-25
Hello.

I have to calculated fields:
,[Contract_NetStartDate] = CASE WHEN ContractStartDate <= @ReportStartDate THEN @ReportStartDate ELSE ContractStartDate END
,[Contract_NetEndDate] = CASE WHEN ContractEndDate >= @ReportEndDate THEN @ReportEndDate ELSE ContractEndDate END

Open in new window


I need to add a third calculated field:
[Contract_NetNumberOfPeriods]

Open in new window


The third field is the number of months between [Contract_NetStartDate] and [Contract_NetEndDate]

Is it possible that some one show me how to obtain that number from the two fields?

Thank you much.
0
Comment
Question by:metropia
7 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 1000 total points
ID: 39943901
DATEDIFF(MONTH, ContractStartDate, ContractEndDate)

This depends on how you want to calculate the number of months.  In the case above...

DATEDIFF(MONTH, '1/31/2014', '2/1/2014') = 1
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39943945
Do you want to do the calculation on the original table columns (OR) on the results of the CASE expressions?

What do you mean by "month"?  That it crossed a calendar month, or that it was a minimum number of days for each "month"?
0
 

Assisted Solution

by:cshark247
cshark247 earned 1000 total points
ID: 39943956
if you want to use original table columns you can use.

SELECT
             [Contract_NetStartDate] = CASE WHEN ContractStartDate <= @ReportStartDate THEN @ReportStartDate ELSE ContractStartDate END
            ,[Contract_NetEndDate] = CASE WHEN ContractEndDate >= @ReportEndDate THEN @ReportEndDate ELSE ContractEndDate END
             ,Contract_NetNumberOfPeriods =  datediff(m,CASE WHEN ContractStartDate <= @ReportStartDate THEN @ReportStartDate ELSE ContractStartDate END,CASE WHEN ContractEndDate >= @ReportEndDate THEN @ReportEndDate ELSE ContractEndDate END)


Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39944000
I wouldn't repeat the expressions twice, too hard to keep in sync later.
0
 

Assisted Solution

by:cshark247
cshark247 earned 1000 total points
ID: 39944065
you are right, it is always good to avoid duplication. Another solution could be to wrap in outer query like this:

SELECT *,
 datediff(m,Contract_NetStartDate , Contract_NetEndDate ) as Contract_NetNumberOfPeriods
 FROM
(
	SELECT 
			 [Contract_NetStartDate] = CASE WHEN ContractStartDate <= @ReportStartDate THEN @ReportStartDate ELSE ContractStartDate END
			,[Contract_NetEndDate] = CASE WHEN ContractEndDate >= @ReportEndDate THEN @ReportEndDate ELSE ContractEndDate END
	
	   
) x

Open in new window

0
 

Author Comment

by:metropia
ID: 39945281
Thank you experts.

I have a question, why when I use:

DATEDIFF(m,Contract_NetStartDate , Contract_NetEndDate)

I get negative numbers, but if I switch to:

DATEDIFF(m, Contract_NetEndDate, Contract_NetStartDate)

Then the number of months is positive

??
0
 
LVL 32

Expert Comment

by:awking00
ID: 39945697
Because your Contract_NetEndDate must be earlier than your Contract_NetStartDate.
For syntax, see here -
http://www.w3schools.com/sql/func_datediff.asp
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

577 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