sql server - How to get difference of days/months/years (datediff) between two dates?

Question

I am looking for a way to implement the SQLServer-function datediff in PostgreSQL. That is, this function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified start date and end date.

datediff(dd, '2010-04-01', '2012-03-05') = 704 // 704 changes of day in this interval
datediff(mm, '2010-04-01', '2012-03-05') = 23  // 23 changes of month
datediff(yy, '2010-04-01', '2012-03-05') = 2   // 2 changes of year

I know I could do 'dd' by simply using subtraction, but any idea about the other two?

Answer 1

Simply subtract them:

SELECT ('2015-01-12'::date - '2015-01-01'::date) AS days;

The result:

 days
------
   11