number of months - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

number of months

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-12-21 : 08:20:05

hi all.
I have dates 200706 and 200504 how can I work out the number of months between each ?

cheers,
jamie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-21 : 08:21:02

use datediff(month, '20050401', '20070601')

KH
[spoiler]Time is always against us[/spoiler]

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-12-21 : 08:41:02

I get this erro :

Conversion failed when converting datetime from character string.

datediff(month, convert(datetime,date1 + '01'), convert(datetime,date2 + '01'))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-21 : 08:43:15

what is the datatype of date1 and date2 ?
varchar or datetime ?

if the date1 and date2 is in string, is it all in same format of YYYYMM ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 09:09:59

SELECT 12 * CAST(LEFT(@DateTo, 4) AS INT) + CAST(RIGHT(@DateTo, 2) AS INT)
- 12 * CAST(LEFT(@DateFrom, 4) AS INT) + CAST(RIGHT(@DateFrom, 2) AS INT)

E 12°55'05.25"
N 56°04'39.16"

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-12-21 : 09:13:41

again.
thank you very much for your assistance !

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-23 : 10:51:45

quote:
Originally posted by jamie

I get this erro :

Conversion failed when converting datetime from character string.

datediff(month, convert(datetime,date1 + '01'), convert(datetime,date2 + '01'))

I don't get such an error...

SELECT DATEDIFF(mm,CONVERT(DATETIME,'200504'+'01'),CONVERT(DATETIME,'200706'+'01'))

Only reason why you might be getting an error is because you haven't told us what the datatype for the two date columns are... if they are integer, then this will work...

SELECT DATEDIFF(mm,CONVERT(CHAR(6),200504)+'01',CONVERT(CHAR(6),200706)+'01')

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-23 : 10:59:44

And, unless I missing something big... it doesn't appear that either of the following produce the correct answer...

DECLARE @DateTo   INT
DECLARE @DateFrom INT
    SET @DateTo   = 200706
    SET @DateFrom = 200504

SELECT 12 * CAST(LEFT(@DateTo, 4) AS INT) + CAST(RIGHT(@DateTo, 2) AS INT)
- 12 * CAST(LEFT(@DateFrom, 4) AS INT) + CAST(RIGHT(@DateFrom, 2) AS INT)

GO
DECLARE @DateTo   CHAR(6)
DECLARE @DateFrom CHAR(6)
    SET @DateTo   = '200706'
    SET @DateFrom = '200504'

SELECT 12 * CAST(LEFT(@DateTo, 4) AS INT) + CAST(RIGHT(@DateTo, 2) AS INT)
- 12 * CAST(LEFT(@DateFrom, 4) AS INT) + CAST(RIGHT(@DateFrom, 2) AS INT)

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-23 : 11:12:55

Slight modification of the non-working code does work, though, and will probably be faster than the CHAR conversion I used...

DECLARE @DateTo   INT
DECLARE @DateFrom INT
    SET @DateTo   = 200706
    SET @DateFrom = 200504

SELECT 12 * CAST(LEFT(@DateTo, 4) AS INT)+CAST(RIGHT(@DateTo, 2) AS INT)
-12 * CAST(LEFT(@DateFrom, 4) AS INT)-CAST(RIGHT(@DateFrom, 2) AS INT)

GO
DECLARE @DateTo   CHAR(6)
DECLARE @DateFrom CHAR(6)
    SET @DateTo   = '200706'
    SET @DateFrom = '200504'

SELECT 12 * CAST(LEFT(@DateTo, 4) AS INT)+CAST(RIGHT(@DateTo, 2) AS INT)
-12 * CAST(LEFT(@DateFrom, 4) AS INT)-CAST(RIGHT(@DateFrom, 2) AS INT)

The problem was that the months for the @DateFrom were being added instead of substracted.

--Jeff Moden

timark
Starting Member

11 Posts

Posted - 2007-12-23 : 13:06:13

OK, so, doesn't the datediff function accept just the month and year as an argument? I know that the format '01/2007' works the same as the format '01/01/2007'. If the format '20070101' works, why doesn't the format '200701' work? Is it just a sql parsing thang? I'm not being argumentative - I'm a relative newbie, and I want to know the nuances of the syntax.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-24 : 01:40:35

and if Dateto and Datefrom are characters, then you can also do

SELECT Datediff(month,@DateFrom+'01',@DateTo+'01' )

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-24 : 02:05:18

quote:
If the format '20070101' works, why doesn't the format '200701' work?

Dunno... not being difficult, either... it's just the way MS made it work... or not...

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-24 : 02:13:26

quote:
Originally posted by timark

OK, so, doesn't the datediff function accept just the month and year as an argument? I know that the format '01/2007' works the same as the format '01/01/2007'. If the format '20070101' works, why doesn't the format '200701' work? Is it just a sql parsing thang? I'm not being argumentative - I'm a relative newbie, and I want to know the nuances of the syntax.

What do you mean by '01/2007' works?
Can you show us an example?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources