January 23, 2015 at 5:08 am
Hello Sir,
I would like to calculate difference between end_date and current date in Months.
Request you can you please help me out for this issue?
And also how we can calculate the sum of difference in months between start_date and end_date for each ID?
CREATE TABLE datedifference (
id INT
,start_date INT
,end_date INT
)
INSERT INTO datedifference VALUES (10,20091202,20100629)
INSERT INTO datedifference VALUES (20,20071202,20090330)
INSERT INTO datedifference VALUES (30,20051202,20101031)
January 23, 2015 at 5:27 am
kiran.rajenimbalkar (1/23/2015)
Hello Sir,I would like to calculate difference between end_date and current date in Months.
Request you can you please help me out for this issue?
And also how we can calculate the sum of difference in months between start_date and end_date for each ID?
CREATE TABLE datedifference (
id INT
,start_date INT
,end_date INT
)
INSERT INTO datedifference VALUES (10,20091202,20100629)INSERT INTO datedifference VALUES (20,20071202,20090330)
INSERT INTO datedifference VALUES (30,20051202,20101031)
Instead of storing dates as integers, it's best if you store them as one of the date data types. Here's an example using the date data type, but it will also work with datetime.
CREATE TABLE dbo.datedifference (
id INT,
start_date date,
end_date date);
INSERT INTO dbo.datedifference VALUES (10, '2009/12/02', '2010/06/29');
INSERT INTO dbo.datedifference VALUES (20, '2007/12/02', '2009/03/30');
INSERT INTO dbo.datedifference VALUES (30, '2005/12/02', '2010/10/31');
SELECT start_date, end_date, DATEDIFF(month, start_date, end_date)
FROM dbo.datedifference;
If you must use integers for the date, then you have to convert the values to a date data type in order to do date math with it. Here's an example:
CREATE TABLE dbo.datedifference2 (
id INT,
start_date integer,
end_date integer);
INSERT INTO dbo.datedifference2 VALUES (10, 20091202, 20100629);
INSERT INTO dbo.datedifference2 VALUES (20, 20071202, 20090330);
INSERT INTO dbo.datedifference2 VALUES (30, 20051202, 20101031);
WITH cteConverted AS (
SELECT convert(datetime, substring(convert(varchar(8), start_date), 1, 4) + '/' +
substring(convert(varchar(8), start_date), 5, 2) + '/' +
substring(convert(varchar(8), start_date), 7, 2)) new_start_date,
convert(datetime, substring(convert(varchar(8), end_date), 1, 4) + '/' +
substring(convert(varchar(8), end_date), 5, 2) + '/' +
substring(convert(varchar(8), end_date), 7, 2)) new_end_date
FROM dbo.datedifference2
)
SELECT new_start_date, new_end_date, DATEDIFF(month, new_start_date, new_end_date)
FROM cteConverted;
All that conversion takes more time than simply using one of the the built-in date data types. Also, the data type will prevent illegal date values from getting into your table. For example, what if a date value got into the integer table with a value of 20150132 or 20150000? You wouldn't be able to convert it to a date at all without getting into some assumptions about start of month, which would also make things more complicated. The bottom line is that the date data types are pretty easy to work with and help you out quite a bit.
January 23, 2015 at 5:29 am
It would help if the dates where stored in date data types.
Using the following would work:
declare @datedifference TABLE
(
id INT
,start_date INT
,end_date INT
)
INSERT INTO @datedifference VALUES (10,20091202,20100629)
INSERT INTO @datedifference VALUES (20,20071202,20090330)
INSERT INTO @datedifference VALUES (30,20051202,20101031)
SELECT id, DATEDIFF(MM, CONVERT(DATE, convert(CHAR,start_date)), convert(DATE, CONVERT(CHAR,end_date))) AS DateDifference
FROM @datedifference
January 23, 2015 at 7:29 am
First of all, you need to define what is meant by "the difference in months" because I'm pretty sure that you didn't mean for the following to represent a whole month and certainly not a whole year.
SELECT DATEDIFF(mm,'2014-12-31','2015-01-01')
,DATEDIFF(yy,'2014-12-31','2015-01-01')
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2015 at 7:31 am
Thanks All for your help it worked for me 🙂
January 23, 2015 at 7:50 am
kiran.rajenimbalkar (1/23/2015)
Thanks All for your help it worked for me 🙂
I'm thinking that since you're doing aggregations, the DURATION in months is important to someone and just using DATEDIFF for such a measurement is flat out wrong.
Again and for example, the following says that a month has passed when only a single day has passed...
SELECT DATEDIFF(mm,'2014-12-31','2015-01-01')
;
... and the following says that 0 months have passed when nearly a whole month has passed.
SELECT DATEDIFF(mm,'2014-12-01','2014-12-31')
;
If this is for project planning in "Man Months", you're also not considering weekends, holidays, or other anomalies.
So, what is this actually for and are you looking for actual duration in months or just the number of month boundaries crossed? I ask because I'm trying to help you from getting beat up by the powers that be if they want something much more accurate than what has been posted, so far.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2015 at 8:31 am
Jeff's right, of course. If this is for project planning, then you're probably going to need a calendar table and figure out the number of working days from start to finish. The duration could be converted to months, but would have to be based on days. A one-day difference being reported as a month is going to get you in trouble. The specs may say "months" but I don't think it'll matter.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply