Geek asks and answers

Sql server date difference if only month and year

Given 2 months and years. Not a single day is specified. I need to calculate the difference between these two dates in SQL. Sample columns in a SQL server DB.

MonthFROM    YearFROM   MonthTo  YearTO

12           2010       1        2012

The output should be 14 months.

+3
source share
4 answers

SQL Fiddle from the following:

SELECT DATEDIFF(month, 
         CONVERT(VARCHAR(19),
                 CAST(MonthFROM as varchar) + '/01/' + CAST(YearFROM as varchar),
                 101), 
         CONVERT(VARCHAR(19), 
                 CAST(MonthTo as varchar) + '/01/' + CAST(YearTO as varchar), 
                 101)) + 1 AS MonthPast
FROM MyTable;

Result:

MONTHPAST
14

Actual difference of months 13, but you received 14as expected result. If your calculation was incorrect, and you really need to 13, then remove it + 1from the request.

+1
source

It is not recommended that you save the date this way.

SELECT DATEDIFF(mm, CAST(YearFROM as varchar) + RIGHT('0' + CAST(MonthFROM as varchar), 2) + '01',
                      CAST(YearTO as varchar) + RIGHT('0' + CAST(MonthTO as varchar), 2) + '01')
+2
source

:

Select (YearTo - YearFrom) * 12 + MonthTo - MonthFrom + 1 as Difference
  from MyTable;
+2

SQL Server 2012

SELECT DATEDIFF(month, DATEFROMPARTS (YearFROM, MonthFROM, 01),
                DATEFROMPARTS (YearTO, MonthTo, 01))
FROM YourTable

SQL Fiddle

0

More articles:


All Articles