No of Months between two dates in YYYYMM format

Question

No of Months between two dates in YYYYMM format

sqlinterset

Ten Centuries

Points: 1341
More actions
June 15, 2015 at 12:53 pm

#301530

I am looking to calculate no of months between two dates which are in YYYYMM format.
Like no of months between 201505 and 201305

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

sqlinterset (6/15/2015)
I am looking to calculate no of months between two dates which are in YYYYMM format.
Like no of months between 201505 and 201305

WITH DATEFIELDS AS (

SELECT DATEFIELD1 = '201503', DATEFIELD2 = '201505'

)

SELECT DATEDIFF(month, LEFT(DATEFIELD1, 4) + '-' + RIGHT(DATEFIELD1, 2) + '-01', LEFT(DATEFIELD2, 4) + '-' + RIGHT(DATEFIELD2, 2) + '-01') AS DIFF_MONTHS

FROM DATEFIELDS;

This assumes you have a character based DATEFIELD1 and DATEFIELD2.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

oops, misread the question.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

sgmunson (6/15/2015)
sqlinterset (6/15/2015)
I am looking to calculate no of months between two dates which are in YYYYMM format.
Like no of months between 201505 and 201305
WITH DATEFIELDS AS (
SELECT DATEFIELD1 = '201503', DATEFIELD2 = '201505'
)
SELECT DATEDIFF(month, LEFT(DATEFIELD1, 4) + '-' + RIGHT(DATEFIELD1, 2) + '-01', LEFT(DATEFIELD2, 4) + '-' + RIGHT(DATEFIELD2, 2) + '-01') AS DIFF_MONTHS
FROM DATEFIELDS;
This assumes you have a character based DATEFIELD1 and DATEFIELD2.

You don't need to do it that lengthy. YYYYMMDD is an ISO standard and SQL Server will identify it regardless of any configuration (YYYY-MM-DD can cause problems).

WITH DATEFIELDS AS (

SELECT DATEFIELD1 = '201503', DATEFIELD2 = '201505'

)

SELECT DATEDIFF(month, DATEFIELD1 + '01', DATEFIELD2 + '01') AS DIFF_MONTHS

FROM DATEFIELDS;

By the way, whenever possible, use date/time data types for this. For full months, you can use day 1. Using date data types allows you to use datetime functions.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 4

sqlinterset (6/15/2015)
I am looking to calculate no of months between two dates which are in YYYYMM format.
Like no of months between 201505 and 201305

I know the question has been answered but I have to ask... Since months can be 28, 29, 30, or 31 days and the two dates can represent partial months, it would be really interesting to find out what you mean by a "month". Are you talking simply about the boundary dates of months or are you looking for a particular duration that would represent a month?

For example, what do you want to return for 20160228 and 20160331?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

one.rob.taylor SSC Enthusiast Points: 149 More actions · Answer 5

To get months and days...

declare @a1 date

declare @a2 date

select @a1 = '2014-01-05',

@a2 = '2014-02-04'

--Select CASE WHEN DAY(@a1) < Day(@a2)

SelectCASE WHEN DAY(@a1) <= Day(@a2) THEN

DATEDIFF(MONTH , @A1,@A2)

ELSE

DATEDIFF(MONTH , @A1,@A2) - 1

END as Months,

CASE WHEN DAY(@a1) <= Day(@a2) THEN

DATEDIFF(DAY,

DateAdd(month, DATEDIFF(MONTH , @A1,@A2), @a1),

@a2

)

else

DATEDIFF(DAY,

DateAdd(month, DATEDIFF(MONTH , @A1,@A2) - 1, @a1),

@a2

)

End as Days

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 6

I still want to know what someone considers to be a month. DATEDIFF(mm,'20150630','20150701') will say that a month has passed when only 1 day will have passed. What is the definition of a "month" for this case? 30 Days? 30.2 days? 4 Weeks? 5-4-4? What?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Jeff Moden (7/16/2015)
I still want to know what someone considers to be a month. DATEDIFF(mm,'20150630','20150701') will say that a month has passed when only 1 day will have passed. What is the definition of a "month" for this case? 30 Days? 30.2 days? 4 Weeks? 5-4-4? What?

I guess the definition of a month for this case is very simple as the OP is not dealing with days, just a year-month combination.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

robert.steele 53480 SSC Enthusiast Points: 145 More actions · Answer 8

Jeff Moden - Thursday, July 16, 2015 8:56 AM
I still want to know what someone considers to be a month. DATEDIFF(mm,'20150630','20150701') will say that a month has passed when only 1 day will have passed. What is the definition of a "month" for this case? 30 Days? 30.2 days? 4 Weeks? 5-4-4? What?

This is the way I solved that issue in my report:

=iif(Day(Fields!"FieldName".Value)<=Day(Today()),DateDiff("m",Fields!"FieldName".Value,Today()),DateDiff("m",Fields!"FieldName".Value,Today())-1)

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 9

robert.steele 53480 - Thursday, December 7, 2017 10:09 AM
Jeff Moden - Thursday, July 16, 2015 8:56 AM
I still want to know what someone considers to be a month. DATEDIFF(mm,'20150630','20150701') will say that a month has passed when only 1 day will have passed. What is the definition of a "month" for this case? 30 Days? 30.2 days? 4 Weeks? 5-4-4? What?
This is the way I solved that issue in my report:
=iif(Day(Fields!"FieldName".Value)<=Day(Today()),DateDiff("m",Fields!"FieldName".Value,Today()),DateDiff("m",Fields!"FieldName".Value,Today())-1)

I'm no genius when it comes to SSRS, SSIS, etc so I have to ask... will that work when the data I a character based value in the format of YYYYMM with no day?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

robert.steele 53480 SSC Enthusiast Points: 145 More actions · Answer 10

Jeff Moden - Thursday, December 7, 2017 3:25 PM
robert.steele 53480 - Thursday, December 7, 2017 10:09 AM
Jeff Moden - Thursday, July 16, 2015 8:56 AM
I still want to know what someone considers to be a month. DATEDIFF(mm,'20150630','20150701') will say that a month has passed when only 1 day will have passed. What is the definition of a "month" for this case? 30 Days? 30.2 days? 4 Weeks? 5-4-4? What?
This is the way I solved that issue in my report:
=iif(Day(Fields!"FieldName".Value)<=Day(Today()),DateDiff("m",Fields!"FieldName".Value,Today()),DateDiff("m",Fields!"FieldName".Value,Today())-1)
I'm no genius when it comes to SSRS, SSIS, etc so I have to ask... will that work when the data I a character based value in the format of YYYYMM with no day?

No it wouldn't work without first using a Convert Command. If you don't have the day value stored you won't be able to get the true accurate date between the two. Sorry I thought you had the day.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 11

robert.steele 53480 - Friday, December 8, 2017 4:11 AM
Jeff Moden - Thursday, December 7, 2017 3:25 PM
robert.steele 53480 - Thursday, December 7, 2017 10:09 AM
Jeff Moden - Thursday, July 16, 2015 8:56 AM
I still want to know what someone considers to be a month. DATEDIFF(mm,'20150630','20150701') will say that a month has passed when only 1 day will have passed. What is the definition of a "month" for this case? 30 Days? 30.2 days? 4 Weeks? 5-4-4? What?
This is the way I solved that issue in my report:
=iif(Day(Fields!"FieldName".Value)<=Day(Today()),DateDiff("m",Fields!"FieldName".Value,Today()),DateDiff("m",Fields!"FieldName".Value,Today())-1)
I'm no genius when it comes to SSRS, SSIS, etc so I have to ask... will that work when the data I a character based value in the format of YYYYMM with no day?
No it wouldn't work without first using a Convert Command. If you don't have the day value stored you won't be able to get the true accurate date between the two. Sorry I thought you had the day.

Thank you, good Sir, for the feedback. I appreciate it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 12

Luis Cazares - Monday, June 15, 2015 1:50 PM
sgmunson (6/15/2015)
sqlinterset (6/15/2015)
I am looking to calculate no of months between two dates which are in YYYYMM format.Like no of months between 201505 and 201305
WITH DATEFIELDS AS (SELECT DATEFIELD1 = '201503', DATEFIELD2 = '201505')SELECT DATEDIFF(month, LEFT(DATEFIELD1, 4) + '-' + RIGHT(DATEFIELD1, 2) + '-01', LEFT(DATEFIELD2, 4) + '-' + RIGHT(DATEFIELD2, 2) + '-01') AS DIFF_MONTHSFROM DATEFIELDS;This assumes you have a character based DATEFIELD1 and DATEFIELD2.
You don't need to do it that lengthy. YYYYMMDD is an ISO standard and SQL Server will identify it regardless of any configuration (YYYY-MM-DD can cause problems).WITH DATEFIELDS AS (SELECT DATEFIELD1 = '201503', DATEFIELD2 = '201505')SELECT DATEDIFF(month, DATEFIELD1 + '01', DATEFIELD2 + '01') AS DIFF_MONTHSFROM DATEFIELDS;By the way, whenever possible, use date/time data types for this. For full months, you can use day 1. Using date data types allows you to use datetime functions.

Not sure why other posts on this thread have more likes but, IMHO, Luis' post is the way to go whether you're playing the code against a table or doing a singleton check. It would work very well as a high performance iTVF.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 13

If the values are integers, I don't see the need to involve date conversions/char stringing at all:

SELECT yyyymm1, yyyymm2, (yyyymm2 / 100 * 12 + yyyymm2 % 100) - (yyyymm1 / 100 * 12 + yyyymm1 % 100) FROM (VALUES(201305,201505),(201505,201505),(201701,201702)) AS testdata(yyyymm1,yyyymm2)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".